TRIGGERS IN MS-SQL

Triggers are special kind of stored procedures that triggers automatically when an event occur. Triggers are written to fire when a specific event on table occurs. Following are the events that may force trigger-:

1) DML operations

Database manipulation operations like INSERT, UPDATE and DELETE.

2) DDL operations

Database definition operations like CREATE, ALTER and DROP.

3) Database operations

Like LOGON, LOGOFF, STARTUP, or SHUTDOWN.

Syntax of DML trigger

CREATE TRIGGER Trigger_Name
ON
Table_Name/View_Name
FOR/AFTER/INSTEAD OF
INSERT/UPDATE/DELETE
AS
   Executable-statements
GO	
	

A simple example to show a DML trigger

Here, I am going to create a trigger that will fire after a row was inserted in a table.

Below is the employee table for which I will create a trigger.

Example of a DML trigger

CREATE TRIGGER InsertInEmployee
ON
Employee
AFTER INSERT -- This will fire after insert on employee
AS
	DECLARE @Age INT;
	SET @Age = (SELECT Age FROM INSERTED AS i) -- INSERTED will keep the new row inserted
	IF(@Age > 60)
	BEGIN
		RAISERROR ('Employee over 60 not allowed.', 16, 1);  
		ROLLBACK TRANSACTION;  
		RETURN   
	END
GO	

Here, I have written a trigger for employee which will be fired when a new record is being inserted. This trigger will not allow to insert a record with age greater than 60.

INSERT INTO Employee (Id, Name, Email, [Address], Age) VALUES (5, 'Deepak', 'd@yopmail.com', 'Dehradun', 59)

After firing above query it will allow to insert, As age is not greater than 60.

Record inserted.

INSERT INTO Employee (Id, Name, Email, [Address], Age) VALUES (5, 'Deepak', 'd@yopmail.com', 'Dehradun', 61)

On firing above query it will not allow to insert, As age is greater than 60.

Record not inserted