Trigger :
A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. Triggers are invoked in response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a server error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.
Triggers are useful in these ways :
- Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
- Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.
Unlike CHECK constraints, triggers can reference columns in other tables. For Example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message. - Triggers can also evaluate the state of a table before and after a data modification and take action's based on that difference.
- Multiple triggers of the same type (INSERT, UPDATE, orDELETE) on a table allow multiple, different actions to take place in response to the same modification statement.
Creating a Trigger Syntax :
CREATE TRIGGER trigger_name
ON { table | vieww }
{
{
{ FOR | AFTER | INSTEAD OF } { [INSERT] [,] [UPDATE] }
AS
---------SQL STATEMENT [.....n ]
}
}
you can use the FOR clause to specify when a trigger is executed :
AFTER :
The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed, AFTER triggers cannot be specified for views, they can only be specified for tables. You can specify multiple AFTER triggers for each triggering action (INSERT, UPDATE, DELETE). If you have multiple AFTER triggers for a table, you can use sp_settriggerorder to define which AFTER trigger fires first and which fires last. All other AFTER triggers besides the first and last fire in an undefined order which you cannot control.
AFTER is the default in SQL Server 2000. You could not specify AFTER or INSERT OF in SQL Server version 7.0 or earlier, all trigger in those version operated as AFTER Triggers.
INSTEAD OF :
The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF triggerr for each triggering action (INSERT, UPDATE and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates to be updatable.
0 Comments