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
Creating Triggers
Triggers can be created and modified with the help of DDL Commands
CREATE TRIGGER TriggerName on TableName
AFTER Insert update Delete
As
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
Example
CREATE TRIGGER TestTrigger ON BOOKS
AFTER Insert
AS
PRINT 'After Trigger Executed'
GO
Here we have created a Trigger ‘TestTrigger’ and it will be executed when you insert a row.
INSERT INTO BOOKS(NAME,PRICE) VALUES('ASP.NET',150.00)
Result is
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.
Example
CREATE TRIGGER TestTriggerTwo ON BOOKS
INSTEAD OF Insert
AS
PRINT 'Instead of Trigger'
GO
INSERT INTO BOOKS(NAME,PRICE) VALUES('VB.NET',150.00)
Result
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’
Trigger Limitations
The following SQL commands are not permitted within the trigger
Restore Database
- Create, Alter or drop database
- Disk Resize
- Disk Init
- Reconfigure
Disabling Triggers
The DML Statement can never bypass the trigger. But system administrator can disable the trigger
Syntax
ALTER TABLE TableName ENABLE or DISABLE TriggerName
Example
ALTER TABLE 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.