Transcript Database Access: SQL Queries

SQL Triggers
SQL Triggers
Jeffrey P. Landry
University of South Alabama
University of South Alabama School of CIS
Last Modified: 7/7/2015
1
SQL Triggers
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
Last Modified: 7/7/2015
2
SQL Triggers
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
Last Modified: 7/7/2015
3
SQL Triggers
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
Last Modified: 7/7/2015
4
SQL Triggers
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
Last Modified: 7/7/2015
5
SQL Triggers
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
Last Modified: 7/7/2015
6
SQL Triggers
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
Last Modified: 7/7/2015
7
SQL Triggers
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
Last Modified: 7/7/2015
8
SQL Triggers
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
Last Modified: 7/7/2015
9
SQL Triggers
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
Last Modified: 7/7/2015
10
SQL Triggers
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
Last Modified: 7/7/2015
11