1 / 11

SQL Triggers

SQL Triggers. Jeffrey P. Landry University of South Alabama. 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 .

onawa
Download Presentation

SQL Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related