What Triggers are
Triggers are a special type of procedure that are executed automatically when an query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify the data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way.
Trigger Creation
The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.
CREATE TRIGGER trigger_name
ON {table_name|view_name}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]
As sql_statements
FOR is same as AFTER but it is for backward compatibility. Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many number of AFTER triggers. Since two or more triggers on table can cause confusion to manage and to debug however its better to place all the related code in one trigger for each action. A view can’t have AFTER triggers.
CREATE TRIGGER Authors_INSERT_UPDATE
ON Auhtors AFTER INSERT,UPDATE
AS
UPDATE Authors
SET ln_name = UPPER(ln_name)
WHERE Author_id in (SELECT author_id from Inserted)
The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that’s created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.
An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.
An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.
INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.
How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.
The syntax of the DROP triggers statement.
DROP TRIGGER trigger_name [,…]
The syntax of the ALTER TRIGGER statement
ALTER TRIGGER trigger_name
ON {table_name|view_name}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]
As sql_statements
Use the triggers very efficiently otherwise it will end up with Performance issues.
|
| Author: Ganesh Kumar O.R. 09 Aug 2005 | Member Level: Bronze Points : 0 |
Hi,
This is a really a nice article. I learned something more about triggers.
Can you tell how it degrades performance??
|
| Author: Jakeer Hussain 08 Nov 2005 | Member Level: Bronze Points : 0 |
Article was good. Can u plz explain in depth.....
|
| Author: Sandhya 29 Jul 2008 | Member Level: Bronze Points : 0 |
Hi vijaya,
One more to go..
Regards
Sandhya
|
| Author: Nimmanagottu Srinivas 08 Aug 2008 | Member Level: Gold Points : 2 |
The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that’s created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.
An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.
An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.
INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.
How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.
|