Triggers
Triggers, automatically executed or fired when some changes
occurs in database like UPDATE, DELETE and INSERT. Triggers are used for
security of the data, so that data remain unchanged until we change it. We can
say they are the special kind of stored procedure.
General Method of Writing Triggers
CREATE TRIGGER TRIGGERNAME
ON
TABLENAME
AFTER EVENT
AS BEGIN
'YOUR LOGIC GOES
HERE'
END
The CREATE TRIGGER statement is used to
create the trigger and ON specifies the table on which trigger will be attached. AFTER EVENT
specifies that this is After Trigger.
Types of Triggers
There two types of triggers
- After Triggers
- Instead Of Triggers
1. After Triggers
These triggers run after an insert, update or delete. Let’s try
to understand with the help of an example.
Create a table employee and insert some dummy records in it.
--CREATING
TABLE EMPLOYEES
CREATE TABLE EMPLOYEES
(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20),
);
INSERT INTO EMPLOYEES VALUES ('SYMOND')
INSERT INTO EMPLOYEES VALUES ('MICHAEL')
INSERT INTO EMPLOYEES VALUES ('GREGG')
INSERT INTO EMPLOYEES VALUES ('JOHN')
At this point I am going to create another table “Audit_Employees”
in which we are going to record the changes that will happen in the main table.
--CREATING
TABLE AUDIT EMPLOYEES
CREATE TABLE AUDIT_EMPLOYEES
(
ID INT,
NAME VARCHAR(20),
[ACTION] VARCHAR(100),
[DATE] DATE
)
Now it’s time to create an after Insert Triggers so here we
go.
After Insert Trigger
--CREATING
INSERT TRIGGERS
CREATE TRIGGER AFTERINSERT
ON
EMPLOYEES
AFTER
INSERT
AS BEGIN
DECLARE
@ID INT
DECLARE
@NAME VARCHAR(20)
DECLARE
@A VARCHAR(30)
SELECT
@ID=I.ID FROM INSERTED I /* INSERTED
IS AUTOMATICALLY CREATED TABLE.ONLY USED IN TRIGGERS.CAN’T USED OUTSIDE THE
TRIGGERS */
SELECT
@NAME=I.NAME FROM INSERTED I
SET
@A='INSERT TRIGGER IS
FIRED'
INSERT
INTO AUDIT_EMPLOYEES VALUES
(@ID, @NAME, @A, GETDATE())
PRINT 'INSERT TRIGGER IS FIRED'
END
INSERTED is a
logical table that is automatically created and is only used in triggers and
not outside of them.
2. Instead Of Triggers
These triggers are used as an interceptor for anything that
anyone tried to do on your table or view. For example if you define and Instead
Of Trigger on your Employee table and anyone tries to delete record from employee
table the record will not get deleted. Let’s understand with the help of an example.
CREATE TRIGGER INSTEAD_OF_DELETE ON
[EMPLOYEES]
INSTEAD OF DELETE
AS
BEGIN
DECLARE
@ID INT;
DECLARE
@NAME VARCHAR(100);
SELECT
@ID=D.EMP_ID FROM DELETED D;
SELECT
@NAME=D.EMP_NAME
FROM DELETED D;
BEGIN
IF(@NAME = 'JOHN')
BEGIN
RAISERROR('CANNOT DELETE WHERE
NAME = JOHN',16,1);
ROLLBACK;
END
ELSE
BEGIN
DELETE
FROM [EMPLOYEES] WHERE
EMP_ID=@EMP_ID;
COMMIT;
INSERT
INTO AUDIT_EMPLOYEES(ID,NAME,[ACTION],[DATE])
VALUES(@ID,@NAME,'DELETED -- INSTEAD OF
DELETE TRIGGER.',GETDATE());
PRINT
'RECORD DELETED -- INSTEAD OF DELETE TRIGGER.'
END
END
END
This trigger will prevent the deletion of records from the
table where Name = 'John'. If user will try to delete such a record where name is
john the Instead Of Trigger will rollback the transaction.
