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 )
  11. GO
  12. CREATE TABLE [dbo].[Blog_tag](
  13. [Blog_id] [int] NOT NULL,
  14. [Tag_id] [int] NOT NULL
  15. ) ON [PRIMARY]
  17. GO
  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. )
  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;
  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;
  6. select @blog_id=i.blog_id from deleted i;
  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.