Skip to main content

triggers

Types of DML Triggers
1.               After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
2.               Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Example
1.   -- First create table Employee_Demo
2.  CREATE TABLE Employee_Demo
3.  (
4.   Emp_ID int identity,
5.   Emp_Name varchar(55),
6.   Emp_Sal decimal (10,2)
7.  )
8.  -- Now Insert records
9.  Insert into Employee_Demo values ('Amit',1000);
10.Insert into Employee_Demo values ('Mohan',1200);
11.Insert into Employee_Demo values ('Avin',1100);
12.Insert into Employee_Demo values ('Manoj',1300);
13.Insert into Employee_Demo values ('Riyaz',1400);
14.--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
15.(
16. Emp_ID int,
17. Emp_Name varchar(55),
18. Emp_Sal decimal(10,2),
19. Audit_Action varchar(100),
20. Audit_Timestamp datetime
21.)
Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example
1.               After Insert Trigger
1.   -- Create trigger on table Employee_Demo for Insert statement
2.  CREATE TRIGGER trgAfterInsert on Employee_Demo
3.  FOR INSERT
4.  AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
5.  select @empid=i.Emp_ID from inserted i;
6.  select @empname=i.Emp_Name from inserted i;
7.  select @empsal=i.Emp_Sal from inserted i;
8.  set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
9.  values (@empid,@empname,@empsal,@audit_action,getdate());
10.PRINT 'AFTER INSERT trigger fired.'
11.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
12. --Now try to insert data in Employee_Demo table
13.insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
14.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_insert_1.png
15. --now select data from both the tables to see trigger action
16.select * from Employee_Demo
17.select * from Employee_Demo_Audit
18.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_insert_2.png
Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.
2.               After Update Trigger
1.   -- Create trigger on table Employee_Demo for Update statement
2.  CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
3.  FOR UPDATE
4.  AS
5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6.  select @empid=i.Emp_ID from inserted i;
7.  select @empname=i.Emp_Name from inserted i;
8.  select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
9.   set @audit_action='Update Record --- After Update Trigger.';
10.if update (Emp_Sal)
11. set @audit_action='Update Record --- After Update Trigger.';
12.insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
13.values (@empid,@empname,@empsal,@audit_action,getdate());
14.PRINT 'AFTER UPDATE trigger fired.'
15.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
16. --Now try to upadte data in Employee_Demo table
17.update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
18.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_update_1.png
19. --now select data from both the tables to see trigger action
20.select * from Employee_Demo
21.select * from Employee_Demo_Audit
22.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_update_2.png
Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.
3.               After Delete Trigger
1.   -- Create trigger on table Employee_Demo for Delete statement
2.  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
3.  FOR DELETE
4.  AS
5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
6.  select @empname=d.Emp_Name from deleted d;
7.  select @empsal=d.Emp_Sal from deleted d;
8.  select @audit_action='Deleted -- After Delete Trigger.';
9.  insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
10.values (@empid,@empname,@empsal,@audit_action,getdate());
11.PRINT 'AFTER DELETE TRIGGER fired.'
12.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
13. --Now try to delete data in Employee_Demo table
14.DELETE FROM Employee_Demo where emp_id = 5
15.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_delete_1.png
16. --now select data from both the tables to see trigger action
17.select * from Employee_Demo
18.select * from Employee_Demo_Audit
19.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/after_delete_2.png
Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.
Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example
1.               Instead of Insert Trigger
1.   -- Create trigger on table Employee_Demo for Insert statement
2.  CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
3.  INSTEAD OF Insert
4.  AS
5.  declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
6.  select @emp_id=i.Emp_ID from inserted i;
7.  select @emp_name=i.Emp_Name from inserted i;
8.  select @emp_sal=i.Emp_Sal from inserted i;
9.  SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
10.BEGIN
11. BEGIN TRAN
12. SET NOCOUNT ON
13. if(@emp_sal>=1000)
14. begin
15. RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
16. else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
17. COMMIT;
18. PRINT 'Record Inserted -- Instead Of Insert Trigger.'
19.END
20.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
21. --Now try to insert data in Employee_Demo table
22.insert into Employee_Demo values ('Shailu',1300)
23.insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
24.--Outputs will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_insert_1.png Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_insert_3.png
25. --now select data from both the tables to see trigger action
26.select * from Employee_Demo
27.select * from Employee_Demo_Audit
28.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_insert_3.png
Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.
2.               Instead of Update Trigger
1.   -- Create trigger on table Employee_Demo for Update statement
2.  CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
3.  INSTEAD OF Update
4.  AS
5.  declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
6.  select @emp_id=i.Emp_ID from inserted i;
7.  select @emp_name=i.Emp_Name from inserted i;
8.  select @emp_sal=i.Emp_Sal from inserted i;
9.  BEGIN
10. BEGIN TRAN
11.if(@emp_sal>=1000)
12. begin
13. RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
14. else begin
15. insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
16. COMMIT;
17. PRINT 'Record Updated -- Instead Of Update Trigger.'; END
18.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
19. --Now try to upadte data in Employee_Demo table
20.update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
21.update Employee_Demo set Emp_Sal = '900' where emp_id = 6
22.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_update_1.png Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_insert_3.png
23. --now select data from both the tables to see trigger action
24.select * from Employee_Demo
25.select * from Employee_Demo_Audit
26.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_update_2.png
Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.
3.               Instead of Delete Trigger
1.   -- Create trigger on table Employee_Demo for Delete statement
2.  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
3.  INSTEAD OF DELETE
4.  AS
5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
6.  select @empname=d.Emp_Name from deleted d;
7.  select @empsal=d.Emp_Sal from deleted d;
8.  BEGIN TRAN if(@empsal>1200) begin
9.   RAISERROR('Cannot delete where salary > 1200',16,1);
10. ROLLBACK;
11. end
12. else begin
13. delete from Employee_Demo where Emp_ID=@empid;
14. COMMIT;
15. insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
16. values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
17. PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
18.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/success.png
19. --Now try to delete data in Employee_Demo table
20.DELETE FROM Employee_Demo where emp_id = 1
21.DELETE FROM Employee_Demo where emp_id = 3
22.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_delete_1.png Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_delete_2.png
23. --now select data from both the tables to see trigger action
24.select * from Employee_Demo
25.select * from Employee_Demo_Audit
26.--Output will be
Description: http://www.dotnet-tricks.com/Content/images/sqlserver/insteadof_delete_3.png
Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.


Labels:

Comments

Post a Comment

Popular posts from this blog

.NET Core API With Dapper, Repository And UnitOfWork

  Introduction In this tutorial, you will use ASP.NET Core Web API to create a web API that returns a list of brands. This article focuses on creating web API using dapper, repository pattern, and UnitOfWork pattern in .NET 8. I will guide you through the steps by step to create web API. HTTP is not just for serving up web pages. HTTP is also a powerful platform for building APIs that expose services and data. HTTP is simple, flexible, and ubiquitous. Almost any platform that you can think of has an HTTP library, so HTTP services can reach a broad range of clients, including browsers, mobile devices, and traditional desktop applications. ASP.NET Core Web API is a framework for building web APIs on top of the .NET Framework. Creating a New Project in Visual Studio Launch Visual Studio and select Create a New Project. Creating a Core Web API Start Visual Studio and choose Create a new project. In the Create a new project dialog, select ASP.NET Core Web API Click Next. Configuring You...
Post a Comment
Read more

.Net framework interview questions

What is the .NET Framework? The .NET Framework is a set of technologies that form an integral part of the .NET Platform. It is Microsoft’s managed code programming model for building applications. The .NET Framework has two main components: Common Language Runtime (CLR): The CLR is one of the foundation in the .NET framework and provides a common set of services for applications developed on Microsoft .Net Technologies. .NET Framework class library: The .NET framework class library is a collection of reusable types and exposes features of the runtime. It contains of a set of classes that is used to access common functionality. What is CTS (Common Type System)? The common type system (CTS) defines how types are declared, used, and managed in the runtime, and is also an important part of the runtime’s support for cross-language integration. The common type system performs the following functions: ·          Establishes a framework that...
Post a Comment
Read more

Move Selected Gridview Rows to Another Gridview in Asp.net

Introduction :  Here I will explain how to move or transfer selected checkbox gridview rows to another  grid v iew in asp.net using c#, vb.net or copy one gridview row to another gridview in asp.net using c#, vb.net. Description :  In previous posts I explained Display images in gridview from database in asp.net, Enable/Disable checkbox in gridview based on condition,  asp.net gridview examples  and  bind data to textbox control in gridview ,  Bind data to dropdownlist  in gridview  and many articles relating to  asp.net,  c#, vb.net, c#,vb.net. Now I will explain how to move selected rows from one gridview to another gridview in asp.netusing c#, vb.net. To implement this first we need to write the code in aspx page like this  < html   xmlns ="http://www.w3.org/1999/xhtml"> < head > ...
Post a Comment
Read more