Triggers in SQL Server

The trigger is a database object similar to a stored procedure that is executed automatically when an event occurs in a database. There are different kinds of events that can activate a trigger like inserting or deleting rows in a table, a user logging into a database server instance, an update to a table column, a table is created, altered, or dropped, etc.

For example, consider a scenario where the salary of an employee in the Employee table is updated. You might want to preserve the previous salary details in a separate audit table before it gets updated to its new value. You can create a trigger to automatically insert updated employee data to the new audit table whenever the Employee table's value is updated.

There are three types of triggers in SQL Server

• DML triggers are automatically fired when an INSERT, UPDATE or DELETE event occurs on a table.
• DDL triggers are automatically invoked when a CREATE, ALTER, or DROP event occurs in a database. It is fired in response to a server scoped or database scoped event.
• Logon trigger is invoked when a LOGON event is raised when a user session is established.

DML Triggers

DML (Data Manipulation Language) trigger is automatically invoked when an INSERT, UPDATE or DELETE statement is executed on a table.

Use the CREATE TRIGGER statement to create a trigger in SQL Server.

CREATE TRIGGER [schema_name.]trigger_name
ON { table_name | view_name }
{ FOR | AFTER | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
    {sql_statements}

In the above syntax:

• schema_name (optional) is the name of the schema where the new trigger will be created.
• trigger_name is the name of the new trigger.
• ON { table_name | view_name } keyword specifies the table or view name on which the trigger will be created.
• AFTER clause specifies the INSERT, UPDATE or DELETE event which will fire the trigger. The AFTER clause specifies that the trigger fires only after SQL Server successfully completes the execution of the action that fired it. All other actions and constraints should be successfully executed before the trigger is fired.
• INSTEAD OF clause is used to skip an INSERT, UPDATE or DELETE statement to a table and instead, executes other statements defined in the trigger. So, the actual INSERT, UPDATE or DELETE statement does not happen at all. INSTEAD OF clause cannot be used on DDL triggers.
• [NOT FOR REPLICATION] clause is specified to instruct the SQL Server not to invoke the trigger when a replication agent modifies the table.
• sql_statements specifies the action to be executed when an event occurs.

DML triggers use two special temporary tables called inserted tables and deleted tables. SQL Server automatically creates and manages these tables. SQL Server uses these tables to find the state of a table before and after a data modification and take action based on that difference.

Let's create a trigger that fires on INSERT, UPDATE and DELETE operation on the Employee table. For that, create a new table EmployeeLog to log all operation performed on the Employee table.

CREATE TABLE EmpLog (
	LogID int IDENTITY(1,1) NOT NULL,
	EmpID int NOT NULL,
	Operation nvarchar(10) NOT NULL,
	UpdatedDate Datetime NOT NULL	
)

In the above table, LogID is the serial number with auto increment, UpdatedDate is the date on which the Employee table was updated. The Operation column stores the type of operation made to the table; either "INSERT", "UPDATE", or "DELETE".

FOR Triggers

The FOR triggers can be defined on tables or views. It fires only when all operations specified in the triggering SQL statement have initiated successfully. All referential cascade actions and constraint checks must also succeed before this trigger fires.

The following FOR trigger fires on the INSERT operation on the Employee table.

CREATE TRIGGER dbo.trgEmployeeInsert
ON dbo.Employee
FOR INSERT	
AS
    INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID ,'INSERT',GETDATE() FROM INSERTED; --virtual table INSERTED

The above will create the trgEmployeeInsert trigger in the <table_name> -> Triggers folder, as shown below.

Execute the select statements on Employee and EmpLog tables to see the existing records.

The following is EmpLog table.

Now, execute the following INSERT statement that will fire the trgEmployeeInsert trigger.

INSERT INTO Employee(FirstName
           ,LastName
           ,EMail
           ,Phone
           ,HireDate
           ,ManagerID
           ,Salary
           ,DepartmentID)
     VALUES('Manisha'
           ,'Dutt'
           ,'[email protected]'
           ,6799878453
           ,'11/07/2015'
           ,5
           ,50000
           ,20)

The above will insert a new row in the Employee table, as shown below.

The trgEmployeeInsert will be fired and insert a row in the EmpLog table, as shown below.

You can see that a new row is inserted in the EmpLog table for each INSERT statement for the Employee table.

Note: For any reason, if the FOR triggers fails then the INSERT will also fail and no rows will be inserted.

AFTER Triggers

The AFTER trigger fires only after the specified triggering SQL statement completed successfully. AFTER triggers cannot be defined on views.

For example, the following trigger will be fired after each UPDATE statement on the Employee table.

CREATE TRIGGER dbo.trgEmployeeUpdate
ON dbo.Employee
AFTER UPDATE
AS
    INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID,'UPDATE', GETDATE() FROM DELETED;

To test this trigger, execute the following UPDATE statement.

UPDATE Employee
SET salary = 55000
WHERE EmployeeID = 2;

Now, select rows from the EmpLog table. The trgEmployeeUpdate trigger should have inserted a new row in the EmpLog table, as shown below.

INSTEAD OF Triggers

An INSTEAD OF trigger allows you to override the INSERT, UPDATE, or DELETE operations on a table or view. The actual DML operations do not occur at all.

The INSTEAD OF DELETE trigger executes instead of the actual delete event on a table or view. In the Instead Of delete trigger example below, when a delete command is issued on the Employee table, a new row is created in the EmpLog table storing the operation as 'Delete', but the row doesn't get deleted.

CREATE TRIGGER dbo.trgInsteadOfDelete 
ON dbo.Employee
INSTEAD OF DELETE
AS
    INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID,'DELETE', GETDATE() FROM DELETED;

Now, execute the following delete statement to test the above trigger.

DELETE FROM Employee
WHERE EmployeeID = 16;

The above statement will fire the trgInsteadOfDelete trigger which will insert a new row in the EmpLog table instead of deleting a row in the Employee table.

The INSTEAD OF DELETE trigger works in the same manner for bulk deletes also. When you run an SQL statement deleting multiple rows, the rows will not be deleted, but equal number of rows gets inserted in the EmpLog table.

Multiple Triggers

In SQL Server, multiple triggers can be created on a table for the same event. There is no defined order of execution for these triggers.

The order of the triggers can be set to First or Last using the stored procedure sp_settriggerorder. There can be only one first or last trigger for a table. All triggers that are fired between the first defined trigger and the last defined trigger are not fired in any guaranteed order. Consider a scenario where there are four or more triggers. After the first defined trigger is fired, there is no defined order of firing for the other triggers until finally, the Last defined trigger is fired.

sp_settriggerorder [ @triggername = ] 'triggername',
[ @order = ] 'value',  
[ @stmttype = ] 'statement_type',  
[ @namespace = { 'DATABASE' | 'SERVER' | NULL } ]

Arguments:

• Triggername is the name of the trigger to be ordered
• @order = Order of the trigger. First, Last or None
• @stmttype = Statement type. INSERT UPDATE, DELETE, LOGON or any TSQL statement event listed in DDL events.
• @namespace specifies whether the DDL trigger was created on Database or Server.

Assume that you have multiple triggers that fire on the update statement on the Employee table. The following example specifies that trigger trgEmployeeUpdate be the first trigger to fire after an UPDATE operation occurs on the Employee table.

sp_settriggerorder @triggername= 'dbo.trgEmployeeUpdate', 
    @order='First', 
    @stmttype = 'UPDATE';

Create a DML Trigger using SSMS

Step 1: Open SSMS and log in to the database server. In Object Explorer, expand the database instance and select the database where you want to create a trigger.

Step 2: Expand the table where you want to create a trigger. Right-click on the Triggers folder and select New Trigger. The CREATE TRIGGER syntax for a new trigger will open in Query Editor.

Step 3: In the Query menu, click Specify Values for Template Parameters.

In the dialog box, specify the trigger name, date created, schema name, author of the trigger, and fill the other parameters. Click Ok.

Step 4: In the Query Editor, enter the SQL statements for the trigger in the commented section – insert statements for trigger here.

Step 5: You can verify the syntax by clicking on Parse under the Query menu.

Step 6: Click Execute to create the trigger.

Step 7: Refresh the table. The new trigger will be created under the Triggers folder of the table.

Thus, you can create triggers in SSMS.