Nov 12

SQL Triggers

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

  1. DML Trigger
    1. After trigger
    2. Instead of trigger
  2. DDL Trigger

DML Trigger

  1. DML triggers execute when a user tries to modify data through a data manipulation language (DML)
  2. DML events are INSERT, UPDATE, or DELETE statements on a table or view
  3. These are frequently used for enforcing business rules and data integrity
  4. 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.

DML After Trigger

DML After Trigger

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 Trigger

  1. DDL triggers execute in response to a variety of data definition language (DDL)
  2. DDL events are CREATE, ALTER, DROP, GRANT, DENY, REVOKE and UPDATE STATISTICS 
  3. Logon triggers fires in response to LOGON event that raised when a user session is being established
  4. 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)[1]', 'varchar(256)'), 
		@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
		@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
		@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
		GETDATE(),
		@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', '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

"SQL

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

  1. TRUNCATE commands does not activate triggers to execute
  2. It is easy to view table and their relationship, constraints, indexes, stored procedures and views but triggers are difficult to view.
  3. Triggers are automatically executing hence they are not visible from application and can be traced through debugging mechanism only.
  4. Easy to forget about triggers if there is no documentation hence it is difficult to figure out for new developers.
  5. 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.

Leave a Reply