The first part of the chapter introduced how you can create a DML trigger and modify its structure. This trigger form can be used to solve different problems. This section describes several application areas for DML triggers (AFTER triggers and INSTEAD OF triggers).
1. AFTER Triggers
As you already know, AFTER triggers fire after the triggering action has been processed. You can specify an AFTER trigger by using either the AFTER or FOR reserved keyword. AFTER triggers can be created only on base tables.
AFTER triggers can be used to perform the following actions, among others:
- Create an audit trail of activities in one or more tables of the database (see Example 14.1)
- Implement business rules (see Example 14.2)
- Enforce referential integrity (see Examples 14.3 and 14.4)
1.1. Creating an Audit Trail
Chapter 12 discussed how you can capture data changes using the mechanism called CDC (change data capture). DML triggers can also be used to solve the same problem. Example 14.1 shows how triggers can create an audit trail of activities in one or more tables of the database.
Example 14.1
/* The audit_budget table is used as an audit trail in the project table */
USE sample;
GO
CREATE TABLE audit_budget
(project_no CHAR(4) NULL,
user_name CHAR(16) NULL,
date DATETIME NULL,
budget_old FLOAT NULL,
budget_new FLOAT NULL);
GO
CREATE TRIGGER modify_budget
ON project AFTER UPDATE
AS IF UPDATE(budget)
BEGIN
DECLARE @budget_old FLOAT
DECLARE @budget_new FLOAT
DECLARE @project_number CHAR(4)
SELECT @budget_old = (SELECT budget FROM deleted)
SELECT @budget_new = (SELECT budget FROM inserted)
SELECT @project_number = (SELECT project_no FROM deleted)
INSERT INTO audit_budget VALUES
(@project_number,USER_NAME(),GETDATEO,@budget_old, @budget_new)
END
Example 14.1 shows how triggers can be used to implement an audit trail of the activity within a table. This example creates the audit_budget table, which stores all modifications of the budget column of the project table. Recording all the modifications of this column will be executed using the modify_budget trigger.
Every modification of the budget column using the UPDATE statement activates the trigger. In doing so, the values of the rows of the deleted and inserted virtual tables are assigned to the corresponding variables @budget_old, @budget_new, and @project_number. The assigned values, together with the username and the current date, will be subsequently inserted into the audit_budget table.
NOTE Example 14.1 assumes that only one row will be updated at a time. Therefore, it is a simplification of a general case in which a trigger handles multirow updates. The implementation of such a general (and complicated) trigger is beyond the introductory level of this book.
If the following Transact-SQL statement is executed,
UPDATE project
SET budget = 200000
WHERE project_no = ‘p2’;
the content of the audit_budget table is as follows:
1.2. Implementing Business Rules
Triggers can be used to create business rules for an application. Example 14.2 shows the creation of such a trigger.
Example 14.2
USE sample;
GO
CREATE TRIGGER total_budget
ON project AFTER UPDATE
AS IF UPDATE (budget)
BEGIN
DECLARE @sum_old1 FLOAT
DECLARE @sum_old2 FLOAT
DECLARE @sum_new FLOAT
SELECT @sum_new = (SELECT SUM(budget) FROM inserted)
SELECT @sum_old1 = (SELECT SUM(p.budget)
FROM project p WHERE p.project_no
NOT IN (SELECT d.project_no FROM deleted d))
SELECT @sum_old2 = (SELECT SUM(budget) FROM deleted)
IF @sum_new > (@sum_old1 + @sum_old2) *1.5
BEGIN
PRINT ‘No modification of budgets’
ROLLBACK TRANSACTION
END
ELSE
PRINT ‘The modification of budgets executed’
END
Example 14.2 creates the rule controlling the modification of the budget for the projects. The total_budget trigger tests every modification of the budgets and executes only such UPDATE statements where the modification does not increase the sum of all budgets by more than 50 percent. Otherwise, the UPDATE statement is rolled back using the ROLLBACK TRANSACTION statement.
1.3. Enforcing Integrity Constraints
As previously stated in Chapter 5, the Database Engine handles two types of integrity constraints:
- Declarative integrity constraints, defined by using the CREATE TABLE and ALTER TABLE statements
- Procedural integrity constraints (handled by triggers)
Generally, you should use declarative integrity constraints, because they are supported by the system and you do not have to implement them. The use of triggers is recommended only for cases where declarative integrity constraints do not exist.
Example 14.3 shows how you can enforce the referential integrity for the employee and works_on tables using triggers.
Example 14.3
USE sample;
GO
CREATE TRIGGER works_integrity
ON works_on AFTER UPDATE
AS IF UPDATE(emp_no)
BEGIN
IF (SELECT employee.emp_no
FROM employee, inserted
WHERE employee.emp_no = inserted.emp_no) IS NULL
BEGIN
ROLLBACK TRANSACTION
PRINT ‘No insertion/modification of the row’
END
ELSE PRINT ‘The row inserted/modified’
END
The works_integrity trigger in Example 14.3 checks one form of the referential integrity for the employee and works_on tables. This means that every UPDATE of the emp_no column in the referenced works_on table is checked, and any violation of the constraint is rejected. The ROLLBACK TRANSACTION statement in the second BEGIN block rolls back the INSERT or UPDATE statement after a violation of the referential constraint.
The trigger in Example 14.3 checks Case 2 for referential integrity between the employee and works_on tables (see the section “Referential Integrity” in Chapter 5).
If the following statement is executed,
update works_on set emp_no = 12 where emp_no = 10102;
the system will display the following error message:
No insertion/modification of the row
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Example 14.4 introduces the trigger that checks for the violation of integrity constraints between the same tables in Case 3.
Example 14.4
USE sample;
GO
CREATE TRIGGER refint_workson2
ON employee AFTER UPDATE
AS IF UPDATE (emp_no)
BEGIN
IF (SELECT COUNT(*)
FROM WORKS_ON, deleted
WHERE works_on.emp_no = deleted.emp_no) > 0
BEGIN
ROLLBACK TRANSACTION
PRINT ‘No modification/deletion of the row’
END
ELSE PRINT ‘The row is deleted/modified’
END
If this statement is executed,
update employee set emp_no = 1 where emp_no = 2581;
the system will display the following error message:
No modification/deletion of the row
Msg 3609, Level 16, State 1, Line 4
The transaction ended in the trigger. The batch has been aborted.
2. INSTEAD OF Triggers
A trigger with the INSTEAD OF clause replaces the corresponding triggering action. It is executed after the corresponding inserted and deleted virtual tables are created, but before any integrity constraint or any other action is performed.
INSTEAD OF triggers can be created on tables as well as on views. When a Transact-SQL statement references a view that has an INSTEAD OF trigger, the database system executes the trigger instead of taking any action against any table. The trigger always uses the information in the inserted and deleted virtual tables built for the view to create any statements needed to build the requested event.
Several restrictions apply to column values that are supplied by an INSTEAD OF trigger:
- Values cannot be specified for computed columns.
- Values cannot be specified for columns with the TIMESTAMP data type.
- Values cannot be specified for columns with an IDENTITY property, unless the IDENTITY_INSERT option is set to ON.
These restrictions are valid only for INSERT and UPDATE statements that reference a base table. An INSERT statement that references a view that has an INSTEAD OF trigger must supply values for all non-nullable columns of that view. (The same is true for an UPDATE statement: an UPDATE statement that references a view that has an INSTEAD OF trigger must supply values for each view column that does not allow nulls and that is referenced in the SET clause.)
3. First and Last Triggers
The Database Engine allows multiple triggers to be created for each table or view and for each modification action (INSERT, UPDATE, and DELETE) on them. By default, there is no defined order in which multiple triggers for a given modification action are executed. Additionally, you can specify the order of multiple triggers defined for a given action. Using the system stored procedure sp_settriggerorder, you can specify that one of the AFTER triggers associated with a table be either the first AFTER trigger or the last AFTER trigger executed for each triggering action. This system procedure has a parameter called @order that can contain three values:
- first Specifies that the trigger is the first AFTER trigger fired for a modification action.
- last Specifies that the trigger is the last AFTER trigger fired for a triggering action.
- none Specifies that there is no specific order in which the trigger should be fired. (This value is generally used to reset a trigger from being either first or last.)
NOTE If you use the ALTER TRIGGER statement to modify the structure of a trigger, the order of that trigger (first or last) will be dropped.
Example 14.5 shows the use of the system stored procedure sp_settriggerorder.
Example 14.5
EXEC sp_settriggerorder @triggername = ‘modify_budget’,
@Border = ‘first’, @stmttype=’update’
NOTE There can be only one first and one last AFTER trigger on a table. The sequence in which all other AFTER triggers fire is undefined.
To display the order of a trigger, you can use the following:
- sp_helptrigger
- OBJECTPROPERTY function
The system procedure sp_helptrigger contains the order column, which displays the order of the specified trigger. Using the OBJECTPROPERTY function, you can specify either ExecIsFirstTrigger or ExecIsLastTrigger as the value of the second parameter of this function. The first parameter is always the identification number of the database object. The OBJECTPROPERTY function displays 1 if the particular property is TRUE.
NOTE Because an INSTEAD OF trigger is fired before data modifications are made to the underlying table, INSTEAD OF triggers cannot be specified as first or last triggers.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.