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 Trigger:-After trigger are further classified into 3 types:- a)After Insert:-Fired after insert operation is performed on the table. b)After Update:Fired after update operation is performed on the table. c)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:-
 a)Instead of Insert:-These will fire when we will insert a record in a table and will perform the specified query instead of insert.
 b)Instead of Update:-These will fire when we will insert a record in a table and will perform the specified query instead of update.
 c)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