What is a trigger

A trigger is a special stored procedure used to safeguard referential integrity, which maintains rules about the relationship between data in different tables. Triggers act when a specific action, such as Insert,delete or update, is performed on a specified table. Triggers can call stored procedures.

Syntax for creating triggers:

Create Trigger[owner.] Trigger Name

on [owner.] Table name

for {Insert,update,delete}

As

Begin

SQL statement (block)

End

Defining a good trigger has an important effect on simplifying the management of data and ensuring the security of the database. Triggers are for the table level, which means that only the owner of the table has permission to create a trigger for the table.

Example:

To insert a new row, you must ensure that the foreign key matches the primary key, and the trigger should first check the connection between the inserted row and the primary key table.

The following triggers compare the title_id of the inserted table with the titles table, assuming that the foreign key is being entered, the null value is not inserted, and if the connection fails, the transaction is rolled back. Insert,update,delete

Create Trigger Forinsertrigl

On Salesdetail

For insert

As

If (SELECT COUNT (*)

From title,inserted

Where titles.title_id=inserted.title_id)!=@ @rowcount

Begin

Rollback Transaction

Print "No,some title_id does not exist in titles."

End

Else

Print "added! All the title_id are exist in titles.

In this case, the @ @rowcount represents the number of rows added to the Salesdetail table, which is also the number of rows added to the inserted table. Detects whether any title_id added to Salesdetail exists in titles by connecting table titles and table inserted. If the number of rows connected (count (*)) is different from the @ @rowcount, one or more inserts are incorrect and the entire transaction is canceled.

Restrictions on triggers:

A table can have up to three triggers, insert,update,delete

Each trigger can be used only for one table

Cannot create triggers on view, temporary table

Truncate table can delete tables, but cannot trigger triggers

Triggers cannot be used on system tables

The performance impact of using triggers properly is positive. When designing and using triggers, it is advantageous to frequently use the sp_depends command to understand the triggers associated with the object, which lists all the objects, tables, and viewport that the trigger affects.

When defining several kinds of database objects, special attention should be paid to stored procedures, indexes and triggers, especially the stored procedures, which have a great effect on database performance.

Description: The two test tables used by Sybase triggers: deleted tables and inserted tables, which are temporary tables with the same structure as the trigger's base table structure, to store data rows related to the modification.