A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status.
Triggers are also useful for enforcing referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use database diagrams, you can create a relationship between tables to automatically create a foreign key constraint.
Triggers are useful in these ways:
title_id
column of the titles
table to cause a deletion of matching rows in other tables. The trigger uses the title_id
column as a unique key to locate matching rows in the titleauthor
, sales
, and roysched
tables.discounts
table) to books (stored in the titles
table) with a price of less than $10.
A trigger is a database object that you create by specifying:
You write triggers in Transact-SQL for Microsoft® SQL Server™ databases or PL/SQL for Oracle databases.
To create a trigger
A new trigger is created with the following SQL statements already defined for you:
Create Trigger /*Trigger_Name*/
on /*Table_name*/
For /*Insert, Update, Delete*/
As
print 'Trigger Fired'
Line | Replace | With |
1 | /*Trigger_Name*/ |
The name you want to assign to the trigger |
2 | /*Table_name*/ |
The name of the table you want to attach the trigger to |
3 | /*Insert, Update, Delete*/ |
The type of transactions that will activate this trigger |
For example, to create a trigger named employee_insupd
for insert and update transactions on the employee
table, you would change the first three lines of the trigger text to the following:
Create Trigger employee_insupd
on employee
For Insert, Update
For examples of triggers for Microsoft SQL Server databases, see Creating Triggers in the SQL Server documentation. For details about the Transact-SQL syntax of triggers, see CREATE TRIGGER Statement in the SQL Server documentation.
You can open a trigger to view or edit the text of an existing trigger that is stored in your database. Triggers are scripted in Transact-SQL for Microsoft® SQL Server™ databases or PL/SQL for Oracle databases.
To open a trigger
-or-
Double-click the name of the trigger that you want to open.
To add a new trigger to the database or to update an existing trigger that you have modified, you can save a trigger.
To save a trigger
A saved trigger appears in the Tables folder in Data View under the table that it's attached to.
To disable the actions defined in the trigger that are automatically carried out on your database immediately following the specified transactions, you can delete a trigger.
You might also want to delete any triggers that enforce referential integrity between related tables if you use database diagrams to design your database. Database diagrams use relationships instead of triggers for this purpose. Thus, if a trigger duplicates a relationship in a database diagram, you should delete either the trigger or the relationship.
To delete a trigger
The trigger is deleted from the database and Data View.