DML Triggers

The estimated reading time for this post is 8 minutes

Data manipulation language (DML) triggers are a powerful tool that enables you to enforce domain, entity, and referential data integrity and business logic. The enforcement of integrity helps you to build reliable applications. DML triggers need to be able to work with both the previous state of the database and its changed state. DML triggers are often added after applications are built, so you need to make sure that adding a trigger does not cause errors in the applications that were designed without them being in place. The SET NOCOUNT ON command helps to avoid the side-effects of triggers.

Triggers are special stored procedures that execute when specific events occur.

There are three types of triggers:

  1. DML Triggers that fire on INSERT, DELETE and UPDATE
  2. DDL Triggers that fire on DDL statements such as CREATE, ALTER and
  3. Logon triggers that fire when a session is established.

You might create multiple triggers to separate the logic that each performs but note that you do not have complete control over the order in which they fire. You can only specify which trigger should fire first and which should fire last. Also there are two types of DML triggers as After and Instead of triggers.

AFTER triggers fire after the data modifications that are part of the event to which they relate complete. This means that an INSERT, UPDATE, or DELETE statement executes and modifies the data in the database. After that modification has completed, AFTER triggers that are associated with that event fire, but still within the same operation that triggered them. Common reasons for implementing AFTER triggers are:

  • Providing auditing of the changes that were made.
  • Implementing complex rules involving the relationship between tables.
  • Implementing default values or calculated values within rows.

In many cases, you can replace trigger-based code with other forms of code. Relationships between tables are more typically implemented by using foreign key constraints. Default values and calculated values are typically implemented by using DEFAULT constraints and persisted calculated columns.

An INSTEAD OF trigger is a special type of trigger that executes alternate code instead of executing the statement from which it was fired. When you use an INSTEAD OF trigger, only the code in the trigger is executed. The original INSERT, UPDATE, or DELETE operation that caused the trigger to fire does not occur. A common use case for INSTEAD OF triggers is to enable views that are based on multiple base tables to be updatable.

DML triggers provide this through a pair of virtual tables called inserted and deleted. These virtual tables are often then joined to the modified table data as part of the logic within the trigger.

A common mistake when you are adding triggers is that if the trigger also causes row modifications (for example, writes an audit row into an audit table), that count is returned in addition to the expected count. You can avoid this situation by using the SET NOCOUNT ON statement. Most triggers should include this statement. Also there are few considerations for using triggers as followings:-

  • Constraints are preferred to triggers.
  • Triggers are complex to debug.
  • Constraint avoid data modification overhead of violation.
  • Triggers use a rowversion store in tempdb database.
  • Excessive trigger usage can impact tempdb performance.
  • Triggers can increase the duration of transactions.

AFTER INSERT/DELETE/UPDATE Triggers

An AFTER INSERT trigger is a trigger that executes whenever an INSERT statement enters data into a table on which the trigger is configured. The action of the INSERT statement is completed before the trigger fires, but the trigger action is logically part of the INSERT operation. When an AFTER INSERT trigger fires, new rows are added to both the base table and the inserted virtual table. The inserted virtual table holds a copy of the rows that have been inserted into the base table.

CREATE TRIGGER TR__SAEEDINSERT ON PERSON.PERSON
	AFTER INSERT AS
		BEGIN 
			UPDATE  PERSON.PERSON SET FirstName=FirstName+'TR_SAEED' WHERE BusinessEntityID=1
		END
INSERT INTO PERSON.BusinessEntity (ModifiedDate) VALUES (DEFAULT)
 
SELECT * FROM PERSON.BusinessEntity
 
INSERT INTO PERSON.PERSON (BusinessEntityID,PersonType,FirstName,LastName)
VALUES (20779,'EM','HAMID','JABARPOURFARD')
 
SELECT * FROM PERSON.PERSON

An AFTER DELETE trigger is a trigger that executes whenever a DELETE statement removes data from a table on which the trigger is configured. The action of the DELETE statement is completed before the trigger fires, but logically within the operation of the statement that fired the trigger. When an AFTER DELETE trigger fires, rows are removed from the base table and added to the deleted virtual table. The deleted virtual table holds a copy of the rows that have been deleted from the base table.

TRUNCATE TABLE is an administrative option that removes all rows from a table. It needs additional permissions above those required for deleting rows. It does not fire any AFTER DELETE triggers that are associated with the table.

CREATE TRIGGER TR_SAEEDDELETE ON PERSON.PERSON
	AFTER DELETE AS
		BEGIN
			UPDATE PERSON.PERSON SET FirstName=D.FirstName, LastName=D.LastName FROM DELETED AS D
			WHERE PERSON.PERSON.BusinessEntityID=1
		END
 
DELETE PERSON.PERSON WHERE BusinessEntityID=20779

An AFTER UPDATE trigger is a trigger that executes whenever an UPDATE statement modifies data in a table on which the trigger is configured. The action of the UPDATE statement is completed before the trigger fires. When an AFTER UPDATE trigger fires, update actions are treated as a set of deletions of how the rows were and insertions of how the rows are now. Rows that are to be modified in the base table are copied to the deleted virtual table and the updated versions of the rows are copied to the inserted virtual table. The inserted virtual table holds a copy of the rows in their modified state, the same as how the rows appear now in the base table.

CREATE TRIGGER TR_SAEEDUPDATE ON PERSON.PERSON
	AFTER UPDATE AS
		BEGIN
			UPDATE PERSON.PERSON SET FirstName=D.FirstName , LastName=D.LastName FROM DELETED AS D
			WHERE PERSON.PERSON.BusinessEntityID=1
			UPDATE PERSON.PERSON SET FirstName=I.FirstName , LastName=I.LastName FROM INSERTED AS I
			WHERE PERSON.PERSON.BusinessEntityID=2
		END
 
UPDATE PERSON.PERSON SET FirstName='KASRA' , LastName='JABARI' WHERE BusinessEntityID=3

 INSTEAD OF Triggers

A very common use case for INSTEAD OF triggers is to enable views that are based on multiple base tables to be updatable. You can define INSTEAD OF triggers on views that have one or more base tables, where they can extend the types of updates that a view can support.

You can specify an INSTEAD OF trigger on both tables and views. You cannot create an INSTEAD OF trigger on views that have the WITH CHECK OPTION clause defined.

CREATE VIEW V_SAEED 
	WITH SCHEMABINDING
		AS 
			SELECT P.BusinessEntityID ,P.FirstName, P.LastName, YEAR(S.OrderDate) AS [YEAR], 
			SUM(S.SubTotal) AS SubTotal, COUNT_BIG(*) AS CountSalesOrder
			FROM Person.Person AS P
			INNER JOIN Sales.Salesorderheader AS S ON S.SalesPersonID=P.BusinessEntityID
			WHERE P.BusinessEntityID IS NOT NULL AND YEAR(S.OrderDate)=2005
			GROUP BY P.BusinessEntityID,P.FirstName, P.LastName, YEAR(S.OrderDate)
 
 
SELECT * FROM V_SAEED
 
ALTER TABLE PERSON.PERSON ADD CONSTRAINT UC_SAEED UNIQUE (BusinessEntityID)
 
CREATE UNIQUE CLUSTERED INDEX IX_SAEED ON V_SAEED (BusinessEntityID)
 
CREATE TRIGGER TR_SAEEDINSTEAD ON V_SAEED
	INSTEAD OF DELETE
		AS
			BEGIN
				UPDATE PERSON.PERSON SET FIRSTNAME='SAEED' WHERE BUSINESSENTITYID=274
			END
 
DELETE V_SAEED WHERE BUSINESSENTITYID=274
 
SELECT * FROM V_SAEED

Recursive Triggers

A recursive trigger is a trigger that performs an action that causes the same trigger to fire again either directly or indirectly. Direct recursion occurs when a trigger fires and performs an action on the same table that causes the same trigger to fire again. Indirect recursion occurs when a trigger fires and performs an action that causes another trigger to fire on a different table, which subsequently causes an update to occur on the original table, which then causes the original trigger to fire again.

Recursive triggers are disabled by default. To enable them:

ALTER DATABASE database_name
SET RECURSIVE_TRIGGERS ON

Be aware of the following considerations of using recursive triggers:-

  • Need careful design and thorough testing to ensure that the 32-level nesting limit is not exceeded
  • Can be difficult to control the order of table updates
  • Can usually be replaced by nonrecursive logic
  • The RECURSIVE_TRIGGERS option only affects direct recursion

It is a common requirement to build logic that only takes action if particular columns are being updated. Be careful not to confuse the UPDATE function with the UPDATE statement. The UPDATE function enables you to detect whether a particular column is being updated in the action of an UPDATE statement.

CREATE TRIGGER TR_WITH_UPDATE ON Person.Person
	AFTER INSERT AS
		BEGIN
			IF UPDATE(BusinessEntityID)
				BEGIN
					INSERT INTO Person.BusinessEntity (ModifiedDate) VALUES (GETDATE())
				END
		END
 
INSERT INTO Person.Person(BusinessEntityID,PersonType,FirstName,LastName) 
VALUES (20779,'EM','HAMID','J.FARD')

Multiple triggers may be created for a single event that you cannot specify the order in which the triggers will fire .

sp_settriggerorder enables you to specify which triggers will fire first and last.

Many developers use triggers in situations where alternatives would be preferable:

  • Use constraints for checking values
  • Use default for values not supplied during inserts
  • Use foreign key constraints to check for referential integrity
  • Use computed and persisted computed columns
  • Use index views for pre-calculating aggregates

I hope this blog post was informative for you; if so, please share it with your colleagues or network. Stay tune for more blog posts. 

 

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz