SQL Triggers

1. SQL Triggers Jeffrey P. Landry University of South Alabama University of South Alabama School of CIS

2. Triggers • A trigger is • procedural SQL code automatically invoked by the DBMS upon the occurrence of a given database manipulation event • associated with INSERT, UPDATE, and DELETE events • associated with a specific table University of South Alabama School of CIS

3. Trigger Firing • Triggers may be invoked BEFORE or AFTER a data row is inserted, updated, or deleted • In SQL-Server, there is not a specific BEFORE trigger, only an AFTER trigger and an INSTEAD OF trigger. • AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE) • INSTEAD OF triggers fire in place of the triggering action and before constraints are processed • Triggers can cause other triggers to fire, and fire recursively University of South Alabama School of CIS

4. Trigger Actions • Triggers perform actions such as: • updating column values in a row • inserting rows • calling a stored procedure • canceling the intended action University of South Alabama School of CIS

5. SQL-Server syntax for creating a trigger CREATE TRIGGER trigger_name ON { table | view } {     { { FOR | AFTER | INSTEAD OF } {[ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }                 AS         [ { IF UPDATE ( column )             [ { AND | OR } UPDATE ( column ) ]                 [ ...n ]         | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )                { comparison_operator } column_bitmask [ ...n ]         } ] sql_statement [...n ]     } } University of South Alabama School of CIS

6. Triggers vs. Stored Procedures • Both contain code in the form of SQL statements that perform actions on the database • But, they are different. • Stored procedures have parameters • Stored procedures are called explicitly, while triggers are implicitly executed, fired by an INSERT, UPDATE, or DELETE event University of South Alabama School of CIS

7. Some Roles Triggers Play • Some critical roles for triggers include • creating audit logs • generating derived column values • enforcing business rules University of South Alabama School of CIS

8. Inserted and Deleted Tables • Inserted and deleted tables • System-defined and managed tables • These tables store copies of rows affected during insert, update, and delete operations • An update operation puts the old row into the deleted and the new row into the inserted. • The inserted and deleted tables are useful in triggers University of South Alabama School of CIS

9. Example – Audit Log CREATE TRIGGER trigUpdateSkillQuestionLog ON dbo.tblSkillQuestion FOR UPDATE AS BEGIN -- add a row to skill question audit file -- reflecting the change caused by UPDATE INSERT INTO logSkillQuestion (logChangeType, logChangeDescription, logPersonId, SkillQuestionID, Question, ChoiceA, ChoiceB, ChoiceC, ChoiceD, CorrectAnswer, Explanation, Objective) SELECT 'UPDATE', ReasonForChange, LastChangePersonId, SkillQuestionID, Question, ChoiceA, ChoiceB, ChoiceC, ChoiceD, CorrectAnswer, Explanation, Objective FROM inserted END University of South Alabama School of CIS

10. More Examples • Audit logs: a transaction log whenever a row in an important table changes • whenever a row in test bank changes • changes to a customer record, student record, or bank account • history of changes can be reconstructed • Derived columns • reducing inventory quantity on hand column whenever a product is ordered • add a penalty to customer account balance if it is paid after the due date University of South Alabama School of CIS

11. Examples with Business Rules • Business rules • Prohibit actions such as giving an employee an excessive raise or a raise outside of the normal business hours • Preventing the update or deletion of an item in the test bank that is currently being used on a live exam • See the example in Transact-SQL Help on the duplicate person, which gets added to a duplicates table or transformed into an update University of South Alabama School of CIS