How to use DML Triggers in SQL Server
A Trigger is a Database Object. The Trigger is basically a special type of Stored Procedure that will automatically fired/executed when a DDL or DML command statement related with the trigger is executed.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers
The Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.
1.Data Manipulation Language (DML) Triggers :
The DMLTriggers will fire when you perform a DML Operation like INSERT,UPDATE and DELETE on the associated Table. The DML Triggers are mainly 2 Types.
AFTER Trigger (using FOR/AFTER CLAUSE) :
These Triggers will fire/execute AFTER an action such as Insert, Update or Delete is performed on the associated Table.
For example, If you insert a record/row in a Table then the trigger related/associated with the Insert event on that Table will fire only after the row passes through all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
Syntax:
Scenario :
Suppose I have a Employee Master table, where I want to create the Triggers for Insert/ Update/Delete events to Track the changes/modifications done on that Table. The Audit( changes/modifications) information will be stored in a different Employee Audit table.
Now lets create the Tbl_Emp_Master and Tbl_Emp_Audit tables as follows...
--Employee Master Table
Create Table Tbl_Emp_Master
( Emp_Id Int Not null,
Emp_Name Varchar(50),
Dept_Name Varchar(50),
Job_Title Varchar (50),
Emp_Status Varchar (50),
Net_Sal Float,
)
--Employee Audit Table
Create Table Tbl_Emp_Audit
(
Audit_Id Int Identity (1,1) Not Null,
EmpId Int Not null,
Audit_Action Varchar(50),
Audit_TimeStamp DateTime Default Getdate()
)
GO
Truncate Table Tbl_Emp_Master
Truncate Table Tbl_Emp_Audit
GO
Next Create the DML Triggers on the table Tbl_Emp_Master as follows...
----------------------------------------------------------
AFTER INSERT Trigger:
Select * From Tbl_Emp_Audit
A Trigger is a Database Object. The Trigger is basically a special type of Stored Procedure that will automatically fired/executed when a DDL or DML command statement related with the trigger is executed.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers
The Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.
1.Data Manipulation Language (DML) Triggers :
The DMLTriggers will fire when you perform a DML Operation like INSERT,UPDATE and DELETE on the associated Table. The DML Triggers are mainly 2 Types.
AFTER Trigger (using FOR/AFTER CLAUSE) :
These Triggers will fire/execute AFTER an action such as Insert, Update or Delete is performed on the associated Table.
For example, If you insert a record/row in a Table then the trigger related/associated with the Insert event on that Table will fire only after the row passes through all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
Syntax:
Create TRIGGER Trigger_Name
ON TABLE
FOR/AFTER INSERT
AS
BEGIN
--Your DML Operation here...
ON TABLE
FOR/AFTER INSERT
AS
BEGIN
--Your DML Operation here...
END
GO
----------------------------------------------------------Scenario :
Suppose I have a Employee Master table, where I want to create the Triggers for Insert/ Update/Delete events to Track the changes/modifications done on that Table. The Audit( changes/modifications) information will be stored in a different Employee Audit table.
Now lets create the Tbl_Emp_Master and Tbl_Emp_Audit tables as follows...
--Employee Master Table
Create Table Tbl_Emp_Master
( Emp_Id Int Not null,
Emp_Name Varchar(50),
Dept_Name Varchar(50),
Job_Title Varchar (50),
Emp_Status Varchar (50),
Net_Sal Float,
)
--Employee Audit Table
Create Table Tbl_Emp_Audit
(
Audit_Id Int Identity (1,1) Not Null,
EmpId Int Not null,
Audit_Action Varchar(50),
Audit_TimeStamp DateTime Default Getdate()
)
GO
Truncate Table Tbl_Emp_Master
Truncate Table Tbl_Emp_Audit
GO
Next Create the DML Triggers on the table Tbl_Emp_Master as follows...
----------------------------------------------------------
AFTER INSERT Trigger:
Now we will create the Insert Trigger on the Table [Tbl_Emp_Master]
Create TRIGGER [dbo].[Trgr_Emp_INSERT]
ON [dbo].[Tbl_Emp_Master]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Select @vEmpId = INSERTED.Emp_Id From INSERTED
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, 'Inserted the Employee Record')
Print 'After INSERT Trigger - [Trgr_Emp_INSERT] has been Fired'
END
GO
After I defined a Insert Trigger on the table [Tbl_Emp_Master] , I am inserting few records into the Table...
Insert Into Tbl_Emp_Master Values ( 123,'Ravi Teja','Business Intelligence','Report Analyst','Active',25000)
ON [dbo].[Tbl_Emp_Master]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Select @vEmpId = INSERTED.Emp_Id From INSERTED
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, 'Inserted the Employee Record')
Print 'After INSERT Trigger - [Trgr_Emp_INSERT] has been Fired'
END
GO
After I defined a Insert Trigger on the table [Tbl_Emp_Master] , I am inserting few records into the Table...
Insert Into Tbl_Emp_Master Values ( 123,'Ravi Teja','Business Intelligence','Report Analyst','Active',25000)
Insert Into Tbl_Emp_Master Values ( 143,'TriVikram','Story Board','Script Analyst','Active',75000)
Insert Into Tbl_Emp_Master Values ( 113,'TP Reddy','Information Technology','Software Engineer','In Active',55000)
Output :
Now we will see how the Insert Trigger fired for each Insert event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.
Insert Into Tbl_Emp_Master Values ( 113,'TP Reddy','Information Technology','Software Engineer','In Active',55000)
Output :
Now we will see how the Insert Trigger fired for each Insert event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.
Please note that SQL Server will use the Logical Table "INSERTED" for performing the Insert operation then it will do the Insert in the actual physical Table.
The INSERTED Table can hold all the records for a while,that we can refer in Trigger event.
Select * From Tbl_Emp_Audit
The INSERTED Table can hold all the records for a while,that we can refer in Trigger event.
Select * From Tbl_Emp_Master
AFTER UPDATE Trigger:
Now we will create the Update Trigger on the Table [Tbl_Emp_Master]
Create TRIGGER [dbo].[Trgr_Emp_UPDATE]
ON [dbo].[Tbl_Emp_Master]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Declare @vAuditAction Varchar(50)
Select @vEmpId = INSERTED.Emp_Id From INSERTED
IF UPDATE(Dept_Name)
BEGIN
SET @vAuditAction = ' Udpated the Employee Dept Name'
END
IF UPDATE(Job_Title)
BEGIN
SET @vAuditAction = 'Udpated the Employee Job Title'
END
IF UPDATE(Emp_Status)
BEGIN
SET @vAuditAction = 'Udpated the Employee Status'
END
IF UPDATE(Net_Sal)
BEGIN
SET @vAuditAction = 'Udpated the Employee Net Salary'
END
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
PrInt 'After UPDATE Trigger - [Trgr_Emp_UPDATE] has been Fired'
END
GO
After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Update Tbl_Emp_Master Set Job_Title='Data Analyst' Where Emp_Id=123
Update Tbl_Emp_Master Set Net_Sal=50000 Where Emp_Id=123
Output :
Now we will see how the Update Trigger fired for each Update event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.
Now we will create the Update Trigger on the Table [Tbl_Emp_Master]
ON [dbo].[Tbl_Emp_Master]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Declare @vAuditAction Varchar(50)
Select @vEmpId = INSERTED.Emp_Id From INSERTED
IF UPDATE(Dept_Name)
BEGIN
SET @vAuditAction = ' Udpated the Employee Dept Name'
END
IF UPDATE(Job_Title)
BEGIN
SET @vAuditAction = 'Udpated the Employee Job Title'
END
IF UPDATE(Emp_Status)
BEGIN
SET @vAuditAction = 'Udpated the Employee Status'
END
IF UPDATE(Net_Sal)
BEGIN
SET @vAuditAction = 'Udpated the Employee Net Salary'
END
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
PrInt 'After UPDATE Trigger - [Trgr_Emp_UPDATE] has been Fired'
END
GO
After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Update Tbl_Emp_Master Set Job_Title='Data Analyst' Where Emp_Id=123
Update Tbl_Emp_Master Set Net_Sal=50000 Where Emp_Id=123
Output :
Now we will see how the Update Trigger fired for each Update event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.
Please note that SQL Server will use the Logical Table "INSERTED" for performing the Update operation then it will do the update in the actual physical Table.
Select * From Tbl_Emp_Master
AFTER DELETE Trigger:
Now we will create the Delete Trigger on the Table [Tbl_Emp_Master]
Create TRIGGER [dbo].[Trgr_Emp_DELETE]
ON [dbo].[Tbl_Emp_Master]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Declare @vAuditAction Varchar(50)
Select @vEmpId = DELETED.Emp_Id From DELETED
SET @vAuditAction = 'Deleted the Employee Record'
ON [dbo].[Tbl_Emp_Master]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Declare @vAuditAction Varchar(50)
Select @vEmpId = DELETED.Emp_Id From DELETED
SET @vAuditAction = 'Deleted the Employee Record'
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
PrInt 'After DELETE Trigger - [Trgr_Emp_DELETE] has been Fired'
END
END
GO
After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Delete From Tbl_Emp_Master Where Emp_Id=143
Output :
After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Delete From Tbl_Emp_Master Where Emp_Id=143
Output :
Now we will see how the Insert Trigger fired for each Insert event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.
Please note that SQL Server will use the Logical Table "DELETED" for performing the Delete operation then it will do the Delete in the actual physical Table.
Select * From Tbl_Emp_Master
Select * From Tbl_Emp_Master
----------------------------------------------------------
INSTEAD OF Trigger (using INSTEAD OF Clause):These Triggers will fire/execute Before an action such as Insert, Update or Delete is performed on the associated Table. This is differs 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/delete to the table.
For example, If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes through all the constraints, such as primary key constraint and some rules. Even If the record/row insertion fails, SQL Server will still fires the Instead of Trigger.
Now I am going explain the INSTEAD OF DELETE Trigger on the table Tbl_Emp_Master for the DML event DELETE.
You can try for other DML Operations(INSTEAD OF INSERT,INSTEAD OF UPDATE) in the same way.
----------------------------------------------------------
INSTEAD OF DELETE (Before Delete)Trigger:
Now we will create the INSTEAD OF DELETE Trigger on the Table [Tbl_Emp_Master] , to perform an operation Instead of Delete when Delete event occurred.
Here in the below example, when we try to Delete an 'Active' Employee record, then it will raise an Error instead of deleting the record.
Create TRIGGER [dbo].[Trgr_Emp_InsteadOfDELETE]
ON [dbo].[Tbl_Emp_Master]
INSTEAD OF DELETE
AS
BEGIN
ON [dbo].[Tbl_Emp_Master]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
Declare @vEmpId Int
Declare @vEmpStatus Varchar(50)
Declare @vAuditAction Varchar(50)
Select @vEmpId = DELETED.Emp_Id From DELETED
Select @vEmpStatus=DELETED.Emp_Status From DELETED
Declare @vEmpId Int
Declare @vEmpStatus Varchar(50)
Declare @vAuditAction Varchar(50)
Select @vEmpId = DELETED.Emp_Id From DELETED
Select @vEmpStatus=DELETED.Emp_Status From DELETED
IF @vEmpStatus='Active'
Begin
RAISERROR('You cannot delete an Active Employee',15,1)
ROLLBACK
SET @vAuditAction = 'The Active Employee Record Cannot be Deleted'
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
End
ELSE
Begin
Delete From Tbl_Emp_Master Where Emp_Id=@vEmpId
SET @vAuditAction = 'The InActive Employee Record Deleted'
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
End
Begin
RAISERROR('You cannot delete an Active Employee',15,1)
ROLLBACK
SET @vAuditAction = 'The Active Employee Record Cannot be Deleted'
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
End
ELSE
Begin
Delete From Tbl_Emp_Master Where Emp_Id=@vEmpId
SET @vAuditAction = 'The InActive Employee Record Deleted'
Insert Into Tbl_Emp_Audit ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
End
Print 'INSTEAD Of DELETE Trigger - [Trgr_Emp_InsteadOfDELETE] has been Fired'
END
GO
After I defined a Instead of DeleteTrigger on the table [Tbl_Emp_Master] , I am trying to Delete and Active Employee records as follows...
After I defined a Instead of DeleteTrigger on the table [Tbl_Emp_Master] , I am trying to Delete and Active Employee records as follows...
Delete From Tbl_Emp_Master Where Emp_Id=143
Output :
Now we will see how the Instead of Delete Trigger fired for the Delete event and stored the log/Audit Information into to the Audit Table from the Logical Table DELETED.
Error Message :
Output :
Now we will see how the Instead of Delete Trigger fired for the Delete event and stored the log/Audit Information into to the Audit Table from the Logical Table DELETED.
Error Message :
Msg 50000, Level 15, State 1, Procedure Trgr_Emp_InsteadOfDELETE, Line 180
You cannot delete an Active Employee
INSTEAD Of DELETE Trigger - [Trgr_Emp_InsteadOfDELETE] has been Fired
Msg 3609, Level 16, State 1, Line 163
The transaction ended in the trigger. The batch has been aborted.
Dropping Triggers :
Drop Trigger [Trgr_Emp_INSERT]
Drop Trigger [Trgr_Emp_UPDATE]
Drop Trigger [Trgr_Emp_DELETE]
Drop Trigger [Trgr_Emp_InsteadOfDELETE]
----------------------------------------------------------
You cannot delete an Active Employee
INSTEAD Of DELETE Trigger - [Trgr_Emp_InsteadOfDELETE] has been Fired
Msg 3609, Level 16, State 1, Line 163
The transaction ended in the trigger. The batch has been aborted.
Dropping Triggers :
Drop Trigger [Trgr_Emp_INSERT]
Drop Trigger [Trgr_Emp_UPDATE]
Drop Trigger [Trgr_Emp_DELETE]
Drop Trigger [Trgr_Emp_InsteadOfDELETE]
----------------------------------------------------------
The DDL Triggers will fire when you perform a DDL Operation like CREATE,ALTER and DROP on the associated Table.We can use only FOR/AFTER clause in DDL Triggers and we cannot use INSTEAD OF clause.
3.CLR triggers :
CLR triggers are special type of Triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for Triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
We can write code for both DDL and DML Triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.
Logon triggers.
4.Logon Triggers :
Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
----------------------------------------------------------
Notes :
We can Call a Stored Procedure or a Function inside a Trigger
Eg:
Create TRIGGER [dbo].[TriggerName]
ON [dbo].[TableName]
INSTEAD OF/AFTER [DML]
AS
BEGIN
SET NOCOUNT ON;
Declare @variable1 Int
Declare @variable2 Varchar(50)
--Calling/Executing a procedure
EXECUTE [dbo].[sp_MyProcedure] Parameter,"Parameter2"
--Calling/Executing a Function
Select * From [dbo].[udf_MyFunction] (Argument1,"Argument2")
END
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.