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)

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,

UPDATE project
	SET budget = 200000
	WHERE project_no = 'p2';Code language: JavaScript (javascript)

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)