In this blog we illustrate the types of a triggers in SQL.

Triggers are those which executes automatically when some event arise with their related tables in database.

Triggers are divided into two types:

1. After Triggers (For Triggers)

      a) After Insert Trigger

      b) After Update Trigger

      c) After Delete Trigger

2. Instead Of Triggers

Firstly, We discuss AFTER TRIGGER(FOR TRIGGER). For this we need to make a table in database, we create the User table.

CREATE TABLE User
(
UserID INT Identity,
UserFirstName Varchar(100),
UserLastName Varchar(100),
Email_Id Varchar(100)
)

We create another table called UserBackUp. This table is used to keep the record of the changes done inside the User table.

To create a UserBackUp table using below code:

CREATE TABLE UserBackUp 
(
UserID INT Identity,
UserFirstName Varchar(100),
UserLastName Varchar(100),
Email_Id Varchar(100)
Action varchar(100),
Timestamp datetime
)

(1) After Insert Trigger

To create AFTER INSERT TRIGGER see the below code:

CREATE TRIGGER trigggerAfterInsert ON [dbo].[User] 
FOR INSERT
AS
	declare @userid int;
	declare @userfirstname varchar(100);
	declare @userlastname varchar(100);
	declare @emailid varchar(100);
             declare @action varchar(100);

	select @userid=i.UserID  from inserted i;	
	select @userfirstname =i.UserFirstName from inserted i;	
	select @userlastname =i.UserLastName from inserted i;	
             select @emailid =i.Email_Id from inserted i;	
	set @action='Inserted Record -- After Insert Trigger.';

	insert into UserBackUp 
           (UserID , UserFirstName, UserLastName, Email_Id, Action, Timestamp) 
	values(@userid, @userfirstname ,@userlastname ,@emailid, @action, getdate());
	PRINT 'AFTER INSERT ON User table trigger fired.'
GO

In the above code we use the table 'inserted'. It is a logical table with a rows which insert into the User table. After inserting the data in user table, we insert the same data values into the UserBackUp table.

To check whether this trigger is working or not, Lets insert a row into the main User table as:

INSERT INTO User VALUES ('Maria','dak','maria.dark@yahoo.com');

After inserting a new record in the User table, trigggerAfterInsert trigger will execute and new record has been inserted with the current timestamp into the UserBackUp table.

(2) After UpdateTrigger

To create an AFTER UPDATE TRIGGER see the below code:

CREATE TRIGGER trigggerAfterUpdate ON [dbo].[User] 
FOR INSERT
AS
	declare @userid int;
	declare @userfirstname varchar(100);
	declare @userlastname varchar(100);
	declare @emailid varchar(100);
             declare @action varchar(100);

	select @userid=i.UserID  from inserted i;	
	select @userfirstname =i.UserFirstName from inserted i;	
	select @userlastname =i.UserLastName from inserted i;	
             select @emailid =i.Email_Id from inserted i;	
	set @action='Inserted Record -- After Insert Trigger.';

if update(UserFirstName)
		set @action=' Record Has Been Updated -- After Update Trigger.';
	if update(UserLastName)
		set @action='Record Has Been Updated -- After Update Trigger.';
    if update(Email_Id)
		set @action='Record Has Been Updated -- After Update Trigger.';

	insert into UserBackUp 
           (UserID , UserFirstName, UserLastName, Email_Id, Action, Timestamp) 
	values(@userid, @userfirstname ,@userlastname ,@emailid, @action, getdate());
	PRINT 'AFTER Update ON User table trigger fired.'
GO

In above code we create an AFTER UPDATE trigger which will update a record in a UserBackUp table. Here we doesn't have any updated table like a logical table “inserted” in AFTER INSERT TRIGGER. In this code we use the “ if update(UserFirstName) “ to check whether the column UserFirstName has been updated or not. Similarly, we check for the column UseLastName and Email_Id.

To check whether this trigger is working or not, Lets update a row into the main User table as:

update Userset UserFirstName='Keet' where UserID=2

After updating a new record in a User table, trigggerAfterUpdate trigger will execute and new record has been inserted with the current timestamp into the UserBackUp table.

(3) After Delete Trigger

To create AFTER DELETE TRIGGER see the below code:

CREATE TRIGGER trigggerAfterDelete ON [dbo].[User] 
FOR INSERT
AS
	declare @userid int;
	declare @userfirstname varchar(100);
	declare @userlastname varchar(100);
	declare @emailid varchar(100);
             declare @action varchar(100);

	select @userid=d.UserID  from deleted d;	
	select @userfirstname =d.UserFirstName from deleted d;	
	select @userlastname =d.UserLastName from deleted d;	
             select @emailid =d.Email_Id from deleted d;	
	set @action='Deleted Record -- After Deleted Trigger.';

	insert into UserBackUp 
           (UserID , UserFirstName, UserLastName, Email_Id, Action, Timestamp) 
	values(@userid, @userfirstname ,@userlastname ,@emailid, @action, getdate());
	PRINT 'AFTER DELETE ON User table trigger fired.'
GO

In above code we use a logical table “deleted' to keep a data for the users those are deleted from the main User table. We use this table to insert the deleted users into the UserBackUp table.

To check whether this trigger is working or not, Lets delete a row from the main User table as:

delete from User where UserID=3;

If we want to enable/disable all the triggers then we need to use the below code:

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

If we want to enable/disable the particular triggers then we need to use the below code:

ALTER TABLE Employee_Test {ENABLE|DISBALE} Trigger_Name

Now discuss the instead of triggers, these types of triggers are generally use as an interceptor for those who are trying to perform an Insert, delete, update operation on table or a on view.

INSTEAD OF TRIGGERS are classified into three types:

1. INSTEAD OF INSERT Trigger.

2. INSTEAD OF UPDATE Trigger.

3. INSTEAD OF DELETE Trigger.

To understand the “instead of trigger' we create “Instead Of Delete” Trigger in the below code:

CREATE TRIGGER trigggerInsteadOfDelete ON [dbo].[User] 
INSTEAD OF DELETE
AS
	declare @userid int;
	declare @userfirstname varchar(100);
	declare @userlastname varchar(100);
	declare @emailid varchar(100);

	select @userid=d.UserID  from deleted d;	
	select @userfirstname =LEN(d.UserFirstName) from deleted d;	
	select @userlastname =d.UserLastName from deleted d;	
             select @emailid =d.Email_Id from deleted d;	
BEGIN
		if(@userfirstname>30)
		begin
			RAISERROR('Cannot delete where userfirstname > 30',16,1);
			ROLLBACK;
		end
                      else
		begin
			delete from User where UserID=@userid;
			COMMIT;
	insert into UserBackUp 
           (UserID , UserFirstName, UserLastName, Email_Id, Action, Timestamp) 
	values(@userid, @userfirstname ,@userlastname ,@emailid, 'Deleted Instead Of Delete Trigger.', getdate());
	PRINT 'INSTEAD Of DELETE ON User table trigger fired.'
    end
END
GO

In above code we create instead of trigger in which we used the “ if(@userfirstname>30) “ to prevent the record for the deletion. We also used a “rollback” to undo the changes of the transaction, if the record has been deleted otherwise we used a “commit' to commit the transaction.

To check whether this trigger is working or not, Lets try to delete a row with the “UserFirstName>30" from the main User table as:

delete from User where UserID=4

After executing the above code we got an error message as defined in the RAISE ERROR statement and no record will be deleted.

Server: Msg 50000, Level 16, State 1, Procedure trigggerInsteadOfDelete, Line 18 Cannot delete where userfirstname > 30