Triggers In SQL Server

Trigger is a special kind of stored procedure that executes in response to certain actions on the table like insertion, updation and deletion. There are 2 types of triggers

1. After Triggers (for triggers)
2. Instead Of Triggers.

After Triggers

After trigger are further classified into 3 types,

1. After Insert: Fired after insert operation is performed on the table.
   
2. After Update: Fired after update operation is performed on the table.
   
3. After Delete: Fired when a record is deleted from a table.

Now we will see the triggers in action from a small example. For this we will first create 2 tables.

CREATE TABLE [dbo].[Blogs](
 [blog_id] [int] IDENTITY(1,1) NOT NULL,
 [blog_title] [varchar](max) NOT NULL,
 [blog_date] [date] NOT NULL,
 [blog_description] [varchar](max) NOT NULL,
 [blog_tags] [varchar](max) NOT NULL,
 [status] [bit] NULL,
 [blog_url] [varchar](max) NULL,
 CONSTRAINT [PK_Blogs] PRIMARY KEY )

GO
CREATE TABLE [dbo].[Blog_tag](
 [Blog_id] [int] NOT NULL,
 [Tag_id] [int] NOT NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Tags](
 [Tags_id] [int] IDENTITY(1,1) NOT NULL,
 [Tag_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY
)

GO

Now, I am going to insert a record in tags table that can be used in trigger.

INSERT INTO [dbo].[Tags]
         ([Tag_name])
   VALUES
         ('Sql Server')
GO

After the new record has been inserted in the tags table .We shall create a trigger on the blogs table that will automatically insert the record in Blog_Tag table.

Create trigger [dbo].[trgAfterInsert] on [dbo].[Blogs]
After Insert
As
declare @blog_id int;
declare @tag_id int;
declare @tagname varchar(50);
select @blog_id=i.blog_id from inserted i;
select @tagname=i.blog_tags from inserted i;
select  @tag_id=Tags_id from Tags where Tag_name=@tagname;

Insert into Blog_tag(Blog_id,Tag_id) values(@blog_id,@tag_id);

Now try to insert a record in blogs table, it will automatically insert record into Blog_tag table.

INSERT INTO [dbo].[Blogs]
       ([blog_title]
       ,[blog_date]
       ,[blog_description]
       ,[blog_tags]
       ,[status]
       ,[blog_url])
 VALUES
       ('Test'
       ,'2016-02-06'
       ,'Test Description'
       ,'Sql Server'
       ,0
       ,'test url')
GO

Similarly we can use after update and after delete trigger. Note:-For after update/delete trigger small change is there in syntax.

Create trigger [dbo].[trgAfterUpdate] on [dbo].[Blogs]
After Update
As
declare @blog_id int;
declare @tagname varchar(50);
declare @tag_id int;
select @blog_id=i.blog_id from inserted i;
select @tagname=i.blog_tags from inserted i;
select  @tag_id=Tags_id from Tags where Tag_name=@tagname;
print @tagname;
Update Blog_tag set Tag_id=@tag_id where  Blog_id=@blog_id;

Similarly we will see for delete also.

Create trigger [dbo].[trgAfterDelete] on [dbo].[Blogs]
After Delete
As
declare @blog_id int;

select @blog_id=i.blog_id from deleted i;

delete from Blog_tag where  Blog_id=@blog_id;

You can clearly view the difference in delete trigger we are using from deleted i instead of inserted i.

Instead of Trigger

These are used when we want to check certain conditions before performing insert, update or delete on a table. These are further classified into 3 types:

1. Instead of Insert: These will fire when we will insert a record in a table and will perform the specified query instead of insert.
   
   
2. Instead of Update: These will fire when we will insert a record in a table and will perform the specified query instead of update.
   
   
3. Instead of delete: These will fire when we will insert a record in a table and will perform the specified query instead of delete. Now i will show how to create instead of delete trigger.

Create trigger[dbo].[trgInsteadDelete] on[dbo].[Blogs]
instead of Delete
As
declare @blog_id int;
declare @tag_id int;
select @blog_id = i.blog_id from deleted i;
select @tag_id = Tag_id from Blog_tag where Blog_id = @blog_id
begin
if (@tag_id is null) begin
rollback;
end
else begin
delete from Blog_tag where Blog_id = @blog_id;
end
end

Similarly we can create instead of insert and instead of update.