Basically,
triggers are classified into two main types:
- AFTER Triggers
- INSTEAD OF Triggers
AFTER Triggers
These
triggers are fired only when all operations specified in the triggering SQL
statement (INSERT, UPDATE or DELETE), including any referential cascade actions
and constraint check, have executed successfully. AFTER is the default if FOR
is the only keyword specified. AFTER triggers cannot be defined on views.
AFTER
TRIGGERS can be classified into three types:
- AFTER INSERT Trigger
- AFTER UPDATE Trigger
- AFTER DELETE Trigger
Example:
CREATE
TABLE Developer
(
in_developer_id
INT IDENTITY,
vc_developer_name
VARCHAR (100),
dc_developer_salary
DECIMAL (10, 2)
)
INSERT
INTO Developer VALUES ('Anil', 1000);
INSERT
INTO Developer VALUES ('Ricky', 1200);
INSERT
INTO Developer VALUES ('Johnnie', 1100);
INSERT
INTO Developer VALUES ('Sebastian', 1300);
INSERT
INTO Developer VALUES ('Maria', 1400);
Creation of
AFTER INSERT TRIGGER to insert the rows inserted into the table into another log
table.
CREATE TABLE DeveloperLog
(
in_developer_id
INT,
vc_developer_name
VARCHAR(100),
dc_developer_salary
DECIMAL(10, 2),
vc_log_action
VARCHAR(100),
dt_log_timestamp
DATETIME
)
AFTER INSERT Trigger – This trigger is fired after an INSERT on the Developer table.
CREATE
TRIGGER trgAfterInsert ON [dbo].[ Developer]
FOR
INSERT
AS
DECLARE @in_developer_id INT;
DECLARE @vc_developer_name VARCHAR (100);
DECLARE @dc_developer_salary DECIMAL (10, 2);
DECLARE @vc_log_action VARCHAR (100);
SELECT @in_developer_id = i.in_developer_id
FROM inserted i;
SELECT @vc_developer_name = i.vc_developer_name
FROM inserted i;
SELECT @dc_ developer_salary = i.dc_
developer_salary FROM inserted i;
SET @vc_log_action = 'Inserted Record --
After Insert Trigger';
INSERT INTO DeveloperLog (in_developer_id,
vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
VALUES (@in_developer_id,
@vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());
GO
The CREATE
TRIGGER statement is used to create the trigger.
THE ON
clause specifies the table name on which the trigger is to be attached.
The FOR
INSERT specifies that this is an AFTER INSERT trigger. In place of FOR
INSERT, AFTER INSERT can be used. Both of them mean the same.
To test the Trigger we insert a row into the Developer table as:
INSERT
INTO Developer VALUES ('Christy', 1500);
Now, a record has been inserted into the Developer table. The AFTER INSERT
trigger attached to this table inserts the record into the DeveloperLog as:
6 Christy
1500.00 Inserted Record -- After
Insert Trigger 2013-01-21 08:05:55.700
AFTER UPDATE Trigger – This trigger is fired
after an update on the table.
CREATE
TRIGGER trgAfterUpdate ON [dbo].[Developer]
FOR
UPDATE
AS
DECLARE @in_developer_id INT;
DECLARE @vc_developer_name VARCHAR (100);
DECLARE @dc_developer_salary DECIMAL (10, 2);
DECLARE @vc_log_action VARCHAR (100);
SELECT @in_developer_id = i.in_developer_id
FROM inserted i;
SELECT @vc_developer_name = i.vc_developer_name
FROM inserted i;
SELECT @dc_developer_salary = i.dc_developer_salary
FROM inserted i;
IF UPDATE (vc_developer_name)
SET @vc_log_action = 'Updated Record
-- After Update Trigger';
IF UPDATE (dc_developer_salary)
SET @vc_log_action = 'Updated Record
-- After Update Trigger';
INSERT INTO DeveloperLog (in_developer_id,
vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
VALUES (@in_developer_id,
@vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());
GO
The AFTER
UPDATE Trigger is created in which the updated record is inserted into the DeveloperLog
table. There is no logical table updated like the logical table
inserted. We can obtain the updated value of a field from the UPDATE (column_name) function.
We can use, IF
UPDATE (vc_developer_name) to check if the column vc_developer_name has
been updated.
To test the
Trigger we update a record in the Developer table as:
UPDATE
Developer SET dc_developer_salary = 2550 WHERE in_developer_id = 6
This
inserts the row into the DeveloperLog table as:
6 Chris 2550.00 Updated Record -- After Update Trigger
2013-01-21 08:06:55.700
AFTER DELETE Trigger – This trigger is fired after a delete
on the table.
CREATE
TRIGGER trgAfterDelete ON [dbo].[Developer]
AFTER
DELETE
AS
DECLARE @in_developer_id INT;
DECLARE @vc_developer_name VARCHAR (100);
DECLARE @dc_developer_salary DECIMAL (10, 2);
DECLARE @vc_log_action VARCHAR (100);
SELECT @in_developer_id = d.in_developer_id
FROM deleted d;
SELECT @vc_developer_name = d.vc_developer_name
FROM deleted d;
SELECT @dc_developer_salary = d.dc_developer_salary
FROM deleted d;
SET @vc_log_action = 'Deleted -- After
Delete Trigger';
INSERT INTO DeveloperLog (in_developer_id,
vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
VALUES (@in_developer_id, @
vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());
GO
In this
trigger, the deleted record’s data is picked from the logical deleted
table and inserted into the DeveloperLog table. Delete the record
from the Developer table.
A record has been inserted into the DeveloperLog table as:
6 Christy 2550.00 Deleted -- After Delete Trigger. 2008-04-26 12:52:13.867
All the
triggers can be enabled / disabled on the table using the statement:
ALTER TABLE Developer
{ENABLE|DISBALE} TRIGGER ALL
Specific
Triggers can be enabled or disabled as:
ALTER TABLE Developer
DISABLE TRIGGER trgAfterDelete
This disables the After Delete Trigger named trgAfterDelete on the specified
table.
Instead Of Triggers – This trigger is executed
in place of the triggering SQL statement (it acts as an interceptor). The logic in the trigger can override
the actions of the triggering statements.
If you
define an Instead Of trigger on a table for the Delete operation, then try
to delete rows, but the rows will not actually get deleted (unless you issue
another delete instruction from within the trigger).
INSTEAD OF TRIGGERS can be classified into three types:
- INSTEAD OF INSERT Trigger
- INSTEAD OF UPDATE Trigger
- INSTEAD OF DELETE Trigger
Example: Create an Instead Of Delete
Trigger:
CREATE
TRIGGER trgInsteadOfDelete ON [dbo].[Developer]
INSTEAD
OF DELETE
AS
DECLARE @in_developer_id INT;
DECLARE @vc_developer_name VARCHAR (100);
DECLARE @dc_developer_salary DECIMAL (10,
2);
SELECT @in_developer_id = d.in_developer_id
FROM deleted d;
SELECT @vc_developer_name = d.vc_developer_name
FROM deleted d;
SELECT @dc_developer_salary = d.dc_developer_salary
FROM deleted d;
BEGIN
IF (@dc_developer_salary > 1200)
BEGIN
RAISERROR ('Cannot delete
where salary > 1200', 16, 1);
ROLLBACK;
END
ELSE
BEGIN
DELETE FROM Developer WHERE in_developer_id
= @in_developer_id;
COMMIT;
INSERT INTO DeveloperLog (in_developer_id,
vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
VALUES (@in_student_id,@vc_developer_name,
@dc_developer_salary, 'Deleted -- Instead Of Delete Trigger', GETDATE());
END
END
GO
This
trigger will prevent the deletion of records from the table where dc_developer_salary
> 1200. If such a record is deleted, the Instead Of Trigger will rollback
the transaction, otherwise the transaction will be committed.
Let’s try to delete a record with the dc_developer_salary >1200:
DELETE
FROM Developer WHERE in_developer_id = 4
This will
print an error message as defined in the RAISERROR statement:
Server: Msg
50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
