Triggers are the type of stored procedures and will be attached to the tables. We can not execute the triggers directory. Triggers will be fired only in repose to Delete, Insert and Update event on the table. Sql Server 2005 has two different types of transaction triggers. They are instead of triggers and after triggers
Triggers can be created and modified with the help of DDL Commands
- CREATETRIGGER TriggerName on TableName
- Trigger Code
- After Triggers
Table can have more than one after trigger for each of the three Insert, Delete and Update events. After triggers are useful for the following
- Complex data validation
- Enforcing complex business rules
- Maintaining modified date columns
- CREATETRIGGER TestTrigger ON BOOKS
- PRINT 'After Trigger Executed'
Here we have created a Trigger ‘TestTrigger’ and it will be executed when you insert a row.
- INSERTINTO BOOKS(NAME,PRICE) VALUES('ASP.NET',150.00)
After Trigger Executed
(1 row(s) affected)
Instead of Triggers
Instead of triggers will be executed instead of the submitted transaction. Hence the submitted transaction will not execute. Each table is limited to use only one Instead of trigger. But instead of triggers can be applied to views.
You can use the Instead of Triggers for the following situation
- When DML statement tried to update the non updatable views, then the Instated of trigger updates the underlying tables.
- When the DML statement attempts to delete a row, an instead of trigger moves the row to an archive table instead.
- CREATETRIGGER TestTriggerTwo ON BOOKS
- PRINT 'Instead of Trigger'
- INSERTINTO BOOKS(NAME,PRICE) VALUES('VB.NET',150.00)
Instead of Trigger
(1 row(s) affected)
Insert statement worked as if one row is affected but the effect of the insert statement was blocked by the instead of trigger. So the print statement is executed and prints the message ‘Instead of Trigger’
The following SQL commands are not permitted within the trigger
- Create, Alter or drop database
- Disk Resize
- Disk Init
The DML Statement can never bypass the trigger. But system administrator can disable the trigger
- ALTERTABLE TableName ENABLE or DISABLE TriggerName
- ALTERTABLE BOOKS
- DISABLE TRIGGER TestTriggerTwo
The above example will disable the trigger “TestTriggerTwo”. In our previous example this triger will be executed instead of the insert statement. After the execution of the Disable Trigger query now you will allowed to insert a row in the Books table.