Triggers

1
Triggers

2
Triggers

• A Triggers is a type of stored procedure defined
  on a specified table
• The trigger code is automatically executed upon
  an INSERT, UPDATE and/or DELETE statement on the
  statement.
• With early database management systems, computer
  scientists referred to the firing of a trigger
  hence the adoption and continued use of the term
  trigger.
• Modern DBMS products like SQL Server use triggers
  to enforce PROCEDURAL INTEGRITY constraint.
  (Business Rules)

3
Triggers

• The trigger and the statement that fires it are
  placed in a single TRANSACTION.
• This transaction can be rolled back or committed
  within this trigger.
• Note the following terms
• trigger action the INSERT, UPDATE and/or DELETE
  that fires the trigger.
• trigger code the trigger Transact-SQL
  statement(s) that execute when the trigger is
  fired.

4
Trigger Syntax

• Triggers have four components
• (1) the trigger name,
• (2) the table or view name to which the trigger
  is assigned,
• (3) the timing of the trigger action and
  associated DML action, and
• (4) the program code to be executed.
• The simplified, general syntax of the CREATE
  TRIGGER statement is shown here

CREATE TRIGGER trigger_name ON table_name
view_name WITH ENCRYPTION FOR
AFTER INSTEAD OF INSERT , UPDATE
, DELETE AS batch code IF UPDATE
(column_name)
5
Trigger Syntax
CREATE TRIGGER trigger_name ON table_name
view_name WITH ENCRYPTION FOR
AFTER INSTEAD OF INSERT , UPDATE
, DELETE AS batch code IF UPDATE
(column_name)

• Trigger_name is the name of the trigger as a
  database object.
• The table or view for a trigger is specified with
  the ON clause.
• If you want to ensure that other users cannot
  view the trigger definition, you can use the WITH
  ENCRYPTION clause. The trigger definition is then
  stored in an unreadable form.
• FOR, AFTER, and INSTEAD OF options define when a
  trigger acts.
• FOR and AFTER clauses both specify that a trigger
  fires after the event that triggers the firing
  AFTER and FOR are synonymous.
• INSTEAD OF specifies that a trigger should
  execute instead of the event that would normally
  activate (fire) the trigger. AFTER triggers are
  only supported on tables, not views.

6
Trigger Syntax
CREATE TRIGGER trigger_name ON table_name
view_name WITH ENCRYPTION FOR
AFTER INSTEAD OF INSERT , UPDATE
, DELETE AS batch code IF UPDATE
(column_name)

• INSERT, UPDATE, and DELETE statements specify
  which DML event will cause a trigger to fire
  DML options can be combined, but you cannot
  specify a DELETE statement option for a trigger
  with an IF UPDATE clause.
• The AS clause is used to specify whether the
  trigger executes procedural and nonprocedural
  T-SQL statements or an IF UPDATE clause.

7
Inserted and Deleted Tables

• Two special tables are used in trigger
  statements the deleted table and the inserted
  table.
• SQL Server 2000 automatically creates and
  manages these tables.
• You can use these temporary, memory-resident
  tables to test the effects of certain data
  modifications and to set conditions for trigger
  actions however, you cannot alter the data in
  the tables directly.

8
Inserted and Deleted Tables

• The deleted table stores copies of the affected
  rows during DELETE and UPDATE statements. During
  the execution of a DELETE or UPDATE statement,
  rows are deleted from the trigger table and
  transferred to the deleted table. The deleted
  table and the trigger table ordinarily have no
  rows in common.
• The inserted table stores copies of the affected
  rows during INSERT and UPDATE statements. During
  an insert or update transaction, new rows are
  added simultaneously to both the inserted table
  and the trigger table. The rows in the inserted
  table are copies of the new rows in the trigger
  table.

9
Inserted and Deleted Tables

• The inserted and deleted tables are used
  primarily in triggers to
• Extend referential integrity between tables.
• Insert or update data in base tables underlying a
  view.
• Check for errors and take action based on the
  error.
• Find the difference between the state of a table
  before and after a data modification and take
  action(s) based on that difference.

10
Example
11
Example 1Updating Employee Salary

• This example uses an audit trail table to store
  information about changes made to employee salary
  data.
• Audit_Employee Table Structure
• Column Name Column Data Type and Size
• emp_ssn CHAR(9)
• old_salary MONEY
• new_salary MONEY
• system_user_name CHAR(20)
• datetime_changed DATETIME

12
The CREATE TABLE Statement for the Audit_Employee
Table

• CREATE TABLE audit_employee (
• emp_ssn CHAR(9),
• old_salary MONEY,
• new_salary MONEY,
• system_user_name CHAR(20),
• datetime_changed DATETIME,
• CONSTRAINT pk_audit_employee
• PRIMARY KEY (emp_ssn, datetime_changed) )

13
Creating/Testing the Update Salary Trigger

• CREATE TRIGGER update_salary
• ON employee AFTER UPDATE
• AS IF UPDATE(emp_salary)
• BEGIN
• DECLARE _at_emp_ssn CHAR(9)
• DECLARE _at_old_salary MONEY
• DECLARE _at_new_salary MONEY
• SELECT _at_old_salary (SELECT emp_salary
• FROM deleted)
• SELECT _at_new_salary (SELECT emp_salary
• FROM inserted)
• SELECT _at_emp_ssn (SELECT emp_ssn
• FROM inserted)
• INSERT INTO audit_employee VALUES
  (_at_emp_ssn, _at_old_salary, _at_new_salary, USER_NAME(),
  GETDATE())
• END
• The trigger fires when an UPDATE occurs to a row
  of the employee table as specified by the ON
  clause.
• The AS clause object is the IF UPDATE
  specification for the emp_salary column of the
  employee table.

14
Creating/Testing the Update Salary Trigger

• CREATE TRIGGER update_salary
• ON employee AFTER UPDATE
• AS IF UPDATE(emp_salary)
• BEGIN
• DECLARE _at_emp_ssn CHAR(9)
• DECLARE _at_old_salary MONEY
• DECLARE _at_new_salary MONEY
• SELECT _at_old_salary (SELECT emp_salary
• FROM deleted)
• SELECT _at_new_salary (SELECT emp_salary
• FROM inserted)
• SELECT _at_emp_ssn (SELECT emp_ssn
• FROM inserted)
• INSERT INTO audit_employee VALUES
  (_at_emp_ssn, _at_old_salary, _at_new_salary, USER_NAME(),
  GETDATE())
• END
• The BEGIN and END statements denote the trigger
  code.
• Three variables are declared that store the
  employee social security number, old salary, and
  new salary (_at_emp_ssn, _at_old_salary, _at_new_salary).
  
• The data types for the variables match the
  associated columns in the employee table.

15
Creating/Testing the Update Salary Trigger

• CREATE TRIGGER update_salary
• ON employee AFTER UPDATE
• AS IF UPDATE(emp_salary)
• BEGIN
• DECLARE _at_emp_ssn CHAR(9)
• DECLARE _at_old_salary MONEY
• DECLARE _at_new_salary MONEY
• SELECT _at_old_salary (SELECT emp_salary
• FROM deleted)
• SELECT _at_new_salary (SELECT emp_salary
• FROM inserted)
• SELECT _at_emp_ssn (SELECT emp_ssn
• FROM inserted)
• INSERT INTO audit_employee VALUES
  (_at_emp_ssn, _at_old_salary, _at_new_salary, USER_NAME(),
  GETDATE())
• END
• SELECT statements store values to the three
  variables by selecting from two virtual tables in
  SQL Server named deleted and inserted (these two
  tables are described later).
• An INSERT statement inserts a row into the
  audit_employee table.
• The USER_NAME( ) and GETDATE( ) functions extract
  the system user name and date of the modification
  from system tables.

16
Testing the Update Salary Trigger

• Test the trigger by executing the
  individual_raise procedure created earlier.
  Assign employee Bock with social security number
  999-11-1111 a 5 raise.
• EXEC individual_raise _at_emp_ssn999111111,
  _at_percent_raise5.0
• SSN Last Name Old Salary
• --------- ---------------------- ----------
• 999111111 Bock 33,217.50
• (1 row(s) affected)
• (1 row(s) affected)
• (1 row(s) affected)
• SSN Last Name New Salary
• --------- ---------------------- ----------
• 999111111 Bock 34,878.38

17
Audit_Employee Table Results

• The update_salary trigger stored one row in the
  audit_employee table.
• System user named dbo made the update at 148
  a.m.
• The audit trail is invisible to any employee
  making salary modifications.
• SELECT emp_ssn "SSN",
• CONVERT(CHAR(10), old_salary, 1) "Old
  Salary",
• CONVERT(CHAR(10), new_salary, 1) "New
  Salary",
• CAST(system_user_name AS CHAR(8)) "Who",
• CAST(datetime_changed AS CHAR(23)) "On
  DateTime"
• FROM audit_employee
• SSN Old Salary New Salary Who On
  DateTime
• --------- ---------- ---------- --------
  -------------------
• 999111111 33,217.50 34,878.38 dbo May 9
  2003 148AM

18
Trigger Example 2 Enforcing a Business Rule

• Example business rule No employee may receive a
  pay raise that exceeds 10 of their current base
  salary.
• The check_salary_raise trigger checks the of a
  raise and if the new employee salary figure is
  10 larger than the old salary figure, the UPDATE
  transaction is canceled through use of a ROLLBACK
  TRANSACTION statement.
• The old and new salary figures are stored to
  variables from the deleted and inserted virtual
  tables, and compared to evaluate the of salary
  raise.

19
Check_Salary_Raise Trigger Code

• CREATE TRIGGER check_salary_raise
• ON employee AFTER UPDATE
• AS IF UPDATE(emp_salary)
• BEGIN
• DECLARE _at_old_salary MONEY
• DECLARE _at_new_salary MONEY
• SELECT _at_old_salary (SELECT emp_salary
  FROM deleted)
• SELECT _at_new_salary (SELECT emp_salary
  FROM inserted)
• IF _at_new_salary gt _at_old_salary 1.1
• BEGIN
• PRINT 'Salary Raise Exceeds Policy
  Limits'
• ROLLBACK TRANSACTION
• END
• ELSE
• BEGIN
• PRINT 'Salary Raise Approved'
• END
• END

20
A Valid Raise Test

• The EXEC of the individual_raise procedure fires
  the check_salary_raise trigger as evidenced by
  the Salary Raise Approved message.
• EXEC individual_raise _at_emp_ssn999111111,
• _at_percent_raise5.0
• SSN Last Name Old Salary
  
• --------- ------------------------- ----------
  
• 999111111 Bock 34,878.38
• Salary Raise Approved
• SSN Last Name New Salary
  
• --------- ------------------------- ----------
  
• 999111111 Bock 36,622.29

21
Audit_Employee Table Results

• The audit_employee table now has two rows a new
  one reflecting the result of the earlier
  update_salary trigger firing.
• SELECT emp_ssn "SSN",
• CONVERT(CHAR(10), old_salary, 1) "Old
  Salary",
• CONVERT(CHAR(10), new_salary, 1) "New
  Salary",
• CAST(system_user_name AS CHAR(8)) "Who",
• CAST(datetime_changed AS CHAR(23)) "On
  DateTime"
• FROM audit_employee
• SSN Old Salary New Salary Who On DateTime
  
• --------- --------- ---------- ----
  -------------------
• 999111111 33,217.50 34,878.38 dbo May 9 2003
  148AM
• 999111111 34,878.38 36,622.29 dbo May 9 2003
  436PM

22
An Invalid Raise Test

• An invalid raise of 11 for employee Bordoloi
  will cause the check_salary_raise trigger to
  display the Salary Raise Exceeds Policy Limits
  message.
• Because the transaction was canceled by the
  ROLLBACK TRANSACTION statement, the
  audit_employee table will not have any record of
  the modification.

23
An Invalid Raise Test (Cont.)

• EXEC individual_raise _at_emp_ssn999666666,
  _at_percent_raise11.0
• SSN Last Name Old Salary
• --------- --------------------- ----------
• 999666666 Bordoloi 60,898.75
• (1 row(s) affected)
• (1 row(s) affected)
• Salary Raise Exceeds Policy Limits

24
Defining the Order of Trigger Execution

• Because SQL Server allows more than one trigger
  for a table or view, it is sometimes important to
  specify the order in which triggers execute.
• By definition, triggers execute in the order in
  which they are created.
• Revise our scenario by dropping the two triggers
  and recreating the update_salary trigger to
  include a COMMIT statement then, recreating the
  check_salary_raise trigger.

25
Redefined Update_Salary Trigger

• CREATE TRIGGER update_salary
• ON employee AFTER UPDATE
• AS IF UPDATE(emp_salary)
• BEGIN
• DECLARE _at_emp_ssn CHAR(9)
• DECLARE _at_old_salary MONEY
• DECLARE _at_new_salary MONEY
• SELECT _at_old_salary (SELECT emp_salary
• FROM deleted)
• SELECT _at_new_salary (SELECT emp_salary
• FROM inserted)
• SELECT _at_emp_ssn (SELECT emp_ssn FROM
  inserted)
• INSERT INTO audit_employee VALUES
• (_at_emp_ssn, _at_old_salary, _at_new_salary,
• USER_NAME(), GETDATE())
• COMMIT
• END

26
Resetting the Trigger Firing Order

• Use the system stored procedure named
  sp_settriggerorder (read set trigger order) for
  an individual event such as an AFTER UPDATE
  transaction.
• Use the parameter _at_order to specify values of
  either (1) FIRST, (2) LAST, or (3) NONE these
  specify if a trigger is to fire FIRST or LAST as
  an AFTER trigger.
• The _at_stmttype parameter specifies the type of DML
  transaction (1) update, (2), delete, or (3)
  insert.
• EXEC sp_settriggerorder _at_triggername'check_salary
  _raise',
• _at_order'first', _at_stmttype'update'

27
Testing the Trigger Firing Order

• Test the trigger firing order by attempting to
  increase Bordolois salary by 11 (violates the
  business rule).
• The check_salary_raise trigger fires first and
  rejects the pay raise. The update operation is
  rolled back.
• EXEC individual_raise _at_emp_ssn999666666,
  _at_percent_raise11.0
• SSN Last Name Old Salary
• --------- -------------------- ----------
• 999666666 Bordoloi 67,597.61
• (1 row(s) affected)
• Salary Raise Exceeds Policy Limits

28
Effect of Trigger Firing Order on the
Audit_Employee Table

• Since Bordolois raise was rolled back, will a
  row still be inserted into the audit_employee
  table?
• No, because row insertions would first be written
  to the inserted virtual table however, the
  check_salary_raise trigger fired and rolled the
  transaction back.
• Rolling back the transaction canceled the raise
  and the update_salary trigger never fires.

29
INSTEAD OF Triggers

• An INSTEAD OF trigger fires in place of a
  triggering event such as an UPDATE or INSERT
  transaction.
• INSTEAD OF triggers execute after SQL Server
  creates the inserted and deleted virtual tables,
  so the data rows for the triggering event are
  stored to these two virtual tables, but any
  existing integrity constraints and triggers
  checking business rules have not yet fired.
• INSTEAD OF triggers can be created on both views
  and tables, whereas AFTER triggers can only be
  created for tablesan important advantage of this
  type of trigger.
• INSTEAD OF triggers use the data rows found in
  the inserted and deleted virtual tables for views
  that are in use to complete any required DML
  transaction.

30
A Project and Equipment View

• Assume the following tables

EQUIPMENT
PROJECT
31
A Project and Equipment View

• The project_equipment view displays information
  about equipment used on various projects.
• The column named eqp_total_value is a computed
  (derived) column in the equipment base table.

32
An Attempted Update

• Project 30 has one printer allocated (eqp_number
  '5678')
• Allocating another printer of the same type to
  project 30 requires an UPDATE operation that
  attempts to use the project_equipment view.
• This yields an error the computed column
  eqp_total_value (referenced as derived in the
  error message) cannot be specified for update
  through use of a view. This is a limitation of
  views.

33
Creating an INSTEAD OF Trigger

• The Trigger fires for the project_equipment view,
  but updates the equipment table directly from
  values in the inserted virtual table.

34
Executing an UPDATE Transaction

• When the UPDATE transaction shown earlier was
  executed, the equipment base table was not
  updated the UPDATE failed.
• Now a re-execution of the UPDATE actually causes
  the UPDATE statement specified as part of the
  INSTEAD OF trigger executes.

35
Executing an UPDATE Transaction

• Now the update executes and the new information
  for the equipment and project is shown by the
  SELECT statement.
• The value stored in the eqp_total_value column
  was automatically updated to 344.00 because this
  column is derived.

36
Summary

• Triggers are database objects that fire based on
  specified DML events.
• Triggers enforce business rules.
• AFTER triggers can enforce integrity constraints
  for tables while INSTEAD OF triggers can enforce
  integrity constraints for both views and tables.
• The order of trigger execution when a table has
  multiple triggers is specified with the
  sp_settriggerorder system procedure.