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)
Creating an Audit Trail
Chapter “Security System of the Database Engine” 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.
/* The audit_budget table is used as an audit trail of activities 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(),GETDATE(),@budget_old, @budget_new) ENDCode language: PHP (php)
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 modification 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 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.
If the following Transact-SQL statement is executed,
the content of the audit_budget table is as follows:
Implementing Business Rules
Triggers can be used to create business rules for an application. Example 14.2 shows the creation of such a trigger.
-- The trigger total_budget is an example of using a trigger to implement -- a business rule 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' ENDCode language: PHP (php)
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.
Enforcing Integrity Constraints
As previously stated in Chapter “Data Definition Language“, a DBMS 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.
USE sample; GO CREATE TRIGGER workson_integrity ON works_on AFTER INSERT, 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' ENDCode language: PHP (php)
The workson_integrity trigger in Example 14.3 checks the referential integrity for the employee and works_on tables. This means that every modification of the emp_no column in the referenced works_on table is checked, and any violation of the constraint is rejected. (The same is true for the insertion of new values into the emp_no column.)
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 1 and case 2 for referential integrity between the employee and works_on tables (see the definition of referential integrity in Chapter “Data Definition Language“).
Example 14.4 introduces the trigger that checks for the violation of integrity constraints between the same tables in case 3 and case 4.
USE sample; GO CREATE TRIGGER refint_workson2 ON employee AFTER DELETE, 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' ENDCode language: PHP (php)