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.