|
A database trigger is procedural code that is automatically executed in response
to certain events on a particular table or view in a database. The trigger is
mostly used for keeping the integrity of the information on the database. You can’t
explicitly invoke triggers. The only way to do this is by performing the
required action to the table that they are assigned to.
Types of
Triggers
There are mainly two types of triggers associated with each of action performed
ie. Insert, Update and Delete. They are:
1.
After/For Triggers
2.
Instead Of Triggers
AFTER/FOR
Triggers
These triggers fire after/for an insert, update or delete on table associated.
They are not supported for views.
AFTER Triggers can be classified further into three types:
i.
AFTER INSERT Trigger.
ii.
AFTER UPDATE Trigger.
iii.
AFTER DELETE Trigger.
Before creating AFTER Trigger I would like to create two tables one which will
keep record of employee (tblEmployee) and another keeping record of action
performed on that table (tblEmployeeTable_Action)
create
table tblEmployeeTable_Action
(nID
int Identity,
sName
varchar(50),
sAddress varchar(100))
CREATE
TABLE tblEmployeeTable_Action
(nID
int,
sName
varchar(50),
sAddress
varchar(100),
sAction
varchar(20),
dDateOfAction datetime)
Now I am going to dump some fake records in table tblEmployee.
insert
into tblEmployee values
('Mac', 'California')
insert
into tblEmployee values
('Raj', 'Banglore')
insert
into tblEmployee values
('John', 'California')
insert
into tblEmployee values
('Mark', 'London')
insert
into tblEmployee values
('Steve', 'California')
AFTER/FOR
INSERT Trigger
This trigger is fired after an INSERT on the table. Basic syntax of creating
AFTER INSERT Trigger is
CREATE
TRIGGER TriggerName
ON
TableName
AFTER/FOR
INSERT
AS
TriggerCode
Example
CREATE
TRIGGER trgAfterInsert
ON tblEmployee
AFTER
INSERT
AS
declare @ID
int;
declare @Name
varchar(100)
declare
@Address varchar(10)
select @ID=nID from inserted
select @Name=sName from
inserted
select @Address=sAddress from
inserted
insert
into tblEmployeeTable_Action
values(@ID,@Name,@Address,'After Insert',getdate())
PRINT
'AFTER INSERT trigger fired.'
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.
In the trigger body, table named inserted has been used. This table is a logical
table and contains the row that has been inserted. I have selected the fields
from the logical inserted table from the row that has been inserted into
different variables, and finally inserted those values into the Audit table.
AFTER/FOR
UPDATE Trigger
This trigger is fired
after an update on the table. The basic syntax is:
CREATE
TRIGGER TriggerName
ON
TableName
AFTER/FOR
UPDATE
AS
TriggerCode
Example
CREATE
TRIGGER trgAfterInsert
ON tblEmployee
AFTER
UPDATE
AS
declare @ID
int;
declare @Name
varchar(100)
declare
@Address varchar(10)
select @ID=nID from inserted
select @Name=sName from
inserted
select @Address=sAddress from
inserted
insert
into tblEmployeeTable_Action
values(@ID,@Name,@Address,'After Update',getdate())
PRINT
'AFTER UPDATE trigger fired.'
GO
AFTER/FOR
Delete Trigger
This trigger is fired
after a delete on the table.
Syntax
CREATE
TRIGGER TriggerName
ON
TableName
AFTER/FOR
DELETE
AS
TriggerCode
CREATE
TRIGGER trgAfterInsert
ON tblEmployee
AFTER
DELETE
AS
declare @ID
int;
declare @Name
varchar(100)
declare
@Address varchar(10)
select @ID=nID from inserted
select @Name=sName from
inserted
select @Address=sAddress from
inserted
insert
into tblEmployeeTable_Action
values(@ID,@Name,@Address,'After Delete',getdate())
PRINT
'AFTER DELETE trigger fired.'
GO
INSTEAD OF
Triggers
While an AFTER trigger can be applied to a table only, an "instead of"
trigger can be associated with either a table or a view. If you use the
INSTEAD OF expression, the trigger starts when the table or view is opened
but before a change has taken place. The difference with the AFTER
trigger is that, this time, you can perform some action(s) before the change is
made on the table or view. This also implies that, if the code of the trigger is
to create a new record, at this time, the record does not yet exist, which means
you cannot catch that record. At this time also, you can prevent the record from
being created
Syntax
CREATE
TRIGGER TriggerName
ON
TableOrViewName
INSTEAD
OF INSERT/UPDATE/DELETE
AS
TriggerCode
Example
CREATE
TRIGGER InsteadOfInsert
ON
tblEmployee
INSTEAD
OF INSERT
AS
print
'Attempt to insert record'
GO
Above Trigger will be fired when someone tries to
new record in tblEmployee and prevent insertion of record into the table.
|