Post
Ask Question

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.

  1. CREATE TABLE [dbo].[Blogs](    
  2.  [blog_id] [int] IDENTITY(1,1) NOT NULL,    
  3.  [blog_title] [varchar](max) NOT NULL,    
  4.  [blog_date] [date] NOT NULL,    
  5.  [blog_description] [varchar](max) NOT NULL,    
  6.  [blog_tags] [varchar](max) NOT NULL,    
  7.  [status] [bit] NULL,    
  8.  [blog_url] [varchar](max) NULL,    
  9.  CONSTRAINT [PK_Blogs] PRIMARY KEY )    
  10.     
  11. GO    
  12. CREATE TABLE [dbo].[Blog_tag](    
  13.  [Blog_id] [int] NOT NULL,    
  14.  [Tag_id] [int] NOT NULL    
  15. ) ON [PRIMARY]    
  16.     
  17. GO    
  18.     
  19.     
  20. CREATE TABLE [dbo].[Tags](    
  21.  [Tags_id] [int] IDENTITY(1,1) NOT NULL,    
  22.  [Tag_name] [varchar](50) NOT NULL,    
  23.  CONSTRAINT [PK_Tags] PRIMARY KEY     
  24. )     
  25.     
  26. GO   
Now, I am going to insert a record in tags table that can be used in trigger.
  1. INSERT INTO [dbo].[Tags]    
  2.          ([Tag_name])    
  3.    VALUES    
  4.          ('Sql Server')    
  5. 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.
  1. Create trigger [dbo].[trgAfterInsert] on [dbo].[Blogs]    
  2. After Insert    
  3. As    
  4. declare @blog_id int;    
  5. declare @tag_id int;    
  6. declare @tagname varchar(50);    
  7. select @blog_id=i.blog_id from inserted i;    
  8. select @tagname=i.blog_tags from inserted i;    
  9. select  @tag_id=Tags_id from Tags where Tag_name=@tagname;    
  10.     
  11. 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.
  1. INSERT INTO [dbo].[Blogs]    
  2.        ([blog_title]    
  3.        ,[blog_date]    
  4.        ,[blog_description]    
  5.        ,[blog_tags]    
  6.        ,[status]    
  7.        ,[blog_url])    
  8.  VALUES    
  9.        ('Test'    
  10.        ,'2016-02-06'    
  11.        ,'Test Description'    
  12.        ,'Sql Server'    
  13.        ,0    
  14.        ,'test url')    
  15. GO   
Similarly we can use after update and after delete trigger. Note:-For after update/delete trigger small change is there in syntax.
  1. Create trigger [dbo].[trgAfterUpdate] on [dbo].[Blogs]    
  2. After Update    
  3. As    
  4. declare @blog_id int;    
  5. declare @tagname varchar(50);    
  6. declare @tag_id int;    
  7. select @blog_id=i.blog_id from inserted i;    
  8. select @tagname=i.blog_tags from inserted i;    
  9. select  @tag_id=Tags_id from Tags where Tag_name=@tagname;    
  10. print @tagname;    
  11. Update Blog_tag set Tag_id=@tag_id where  Blog_id=@blog_id;    
Similarly we will see for delete also.
  1. Create trigger [dbo].[trgAfterDelete] on [dbo].[Blogs]    
  2. After Delete    
  3. As    
  4. declare @blog_id int;    
  5.     
  6. select @blog_id=i.blog_id from deleted i;    
  7.     
  8. 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.

  1. Create trigger[dbo].[trgInsteadDelete] on[dbo].[Blogs]  
  2. instead of Delete  
  3. As  
  4. declare @blog_id int;  
  5. declare @tag_id int;  
  6. select @blog_id = i.blog_id from deleted i;  
  7. select @tag_id = Tag_id from Blog_tag where Blog_id = @blog_id  
  8. begin  
  9. if (@tag_id is null) begin  
  10. rollback;  
  11. end  
  12. else begin  
  13. delete from Blog_tag where Blog_id = @blog_id;  
  14. end  
  15. end  
Similarly we can create instead of insert and instead of update.