A SQL Trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.
In my previous post we created employee table. So here I will use same table to create history of employee. This post explains how to create history on performing Insert, update or delete operation on employee table.
So let’s create EmployeeHistory table first,
CREATE TABLE [dbo].[EmployeeHistory]( [EmployeeHistoryId] [bigint] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NOT NULL, [EmployeeName] [varchar](100) NULL, [DepartmentId] [int] NULL, [ReportTo] [int] NULL, [Salary] [int] NOT NULL, [Action] [varchar](20) NOT NULL, CONSTRAINT [PK_EmployeeHistory] PRIMARY KEY CLUSTERED ( [EmployeeHistoryId] ASC ) ) ON [PRIMARY]
Type of Trigger
There are two types of triggers in SQL Server database
- DML Trigger
- After trigger
- Instead of trigger
- DDL Trigger
- DML triggers execute when a user tries to modify data through a data manipulation language (DML)
- DML events are INSERT, UPDATE, or DELETE statements on a table or view
- These are frequently used for enforcing business rules and data integrity
- Example: Creating audit log history for employee table
1. AFTER TRIGGER
- AFTER trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.
- AFTER triggers cannot be defined on views.
Here we can create separate after triggers for INSERT, UPDATE, DELETE as well as single trigger too. I will show you how to write single trigger to perform all operations.
CREATE TRIGGER [dbo].[AfterTriggerEmployee] ON [dbo].[Employee] FOR INSERT, UPDATE, DELETE AS BEGIN IF EXISTS (SELECT 1 FROM inserted) BEGIN IF EXISTS (SELECT 1 FROM deleted) BEGIN INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action]) SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Update' FROM inserted u; END ELSE BEGIN INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action]) SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Insert' FROM inserted i; END END ELSE BEGIN INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action]) SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Delete' FROM deleted d; END END
Now, Perform INSERT, UPDATE and DELETE Operation one by one and finally check the EmployeeHistory table
INSERT INTO Employee(EmployeeName, DepartmentId, ReportTo, Salary) VALUES('Trigger Test Employee', 3, 3, 23000) UPDATE Employee SET EmployeeName = 'Trigger Test Employee 1', Salary = 26000 WHERE EmployeeName = 'Trigger Test Employee' DELETE FROM Employee WHERE EmployeeName = 'Trigger Test Employee 1' SELECT * FROM EmployeeHistory
It will display following output on SQL console window.
2. INSTEAD OF TRIGGER
- This trigger is executed before the triggering SQL statement, therefore, overriding the actions of the triggering statements
- INSTEAD OF cannot be specified for DDL or logon triggers
- Only one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view
- Actual insert, delete or update operation do not occur at all
- INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION
- INSTEAD OF TRIGGER can be defined on views
Suppose I want to give limited access to my application, to manage this I will create view so that developers do not get access to the tables for DML operation. In sql server we can achieve this through the INSTEAD OF triggers.
Let’s create SQL View on Employee and Department table by joining tables
CREATE VIEW [dbo].[VW_Employee_GetAll] AS SELECT e.EmployeeName, e.ReportTo, e.Salary, d.Name AS Designation FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id
Now, create INSTEAD OF trigger for insert, update and delete operation on view VW_Employee_GetAll
- DDL triggers execute in response to a variety of data definition language (DDL)
- DDL events are CREATE, ALTER, DROP, GRANT, DENY, REVOKE and UPDATE STATISTICS
- Logon triggers fires in response to LOGON event that raised when a user session is being established
- DDL triggers can be DATABASE scoped or SERVER scoped.
SQL Server itself provides report to see the schema changes. However, if you wish you can write your own trigger to track developers who have changed the schema.
Here I am creating database level DDL Trigger which will track information of procedures, tables and functions which are created, altered or dropped. For this I will use SQL Server EVENTDATA() object and will read the xml object.
EVENTDATA() – captures all the necessary information on the lines, what/who/when in the form of an XML string.
/EVENT_INSTANCE/DatabaseName – Returns the databasename on which the DDL operation was performed.
/EVENT_INSTANCE/EventType – Returns the DDL operation, Create/Alter/Drop
/EVENT_INSTANCE/ObjectName – Returns name of a table/stored procedure/index etc.,
/EVENT_INSTANCE/ObjectType – Returns object type. Ex. StoredProcedure, Table, Index etc.,
/EVENT_INSTANCE/TSQLCommand – Returns complete T-SQL command that made the modification.
/EVENT_INSTANCE/LoginName – Returns the login name which made the modification.
To keep track of these object we first need to create table i.e. DDLSchemaChangesHistory so let’s create it.
CREATE TABLE [dbo].[DDLSchemaChangesHistory]( [Id] [int] IDENTITY(1,1) NOT NULL, [ObjectName] [varchar](255) NOT NULL, [ObjectType] [varchar](255) NOT NULL, [OperationType] [varchar](255) NOT NULL, [SQLCommand] [varchar](8000) NOT NULL, [Time] [datetime] NOT NULL, [LoginName] [varchar](255) NOT NULL, CONSTRAINT [PK_DDLSchemaChangesHistory] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY]
Now create DDL Trigger
CREATE TRIGGER DDLTriggerCsharpDocs ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION AS BEGIN DECLARE @EventData XML SET @EventData = EVENTDATA() INSERT INTO DDLSchemaChangesHistory(ObjectName, ObjectType, OperationType, SQLCommand, [Time], LoginName) VALUES ( @EventData.value('(/EVENT_INSTANCE/ObjectName)', 'varchar(256)'), @EventData.value('(/EVENT_INSTANCE/ObjectType)', 'varchar(25)'), @EventData.value('(/EVENT_INSTANCE/EventType)', 'varchar(50)'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'varchar(max)'), GETDATE(), @EventData.value('(/EVENT_INSTANCE/LoginName)', 'varchar(256)') ) END
Now let’s test it using below scripts
ALTER TABLE DDLSchemaChangesHistory ADD ServerUser VARCHAR(50) GO ALTER TABLE DDLSchemaChangesHistory DROP COLUMN ServerUser GO SELECT * FROM DDLSchemaChangesHistory GO
After successful DDL trigger execution it showing me following output
Enable and Disable Trigger
To disable the trigger use below query
DISABLE TRIGGER [AfterTriggerEmployee] ON Employee;
To enable trigger use below query
ENABLE TRIGGER [AfterTriggerEmployee] ON Employee;
Limitations of Triggers
- TRUNCATE commands does not activate triggers to execute
- It is easy to view table and their relationship, constraints, indexes, stored procedures and views but triggers are difficult to view.
- Triggers are automatically executing hence they are not visible from application and can be traced through debugging mechanism only.
- Easy to forget about triggers if there is no documentation hence it is difficult to figure out for new developers.
- Triggers automatically runs every time when database field updated which leads to run system slower
Personally, I would recommend to avoid triggers and use stored procedures. In one on my project I used triggers to keep track of audit history which was a separate database. To avoid code duplication (stored procedures to INSERT, UPDATE and DELETE) and stored procedure dependency I used triggers.