Sql triggers
Download
1 / 11

- PowerPoint PPT Presentation


  • 227 Views
  • Updated On :

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 .

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '' - onawa


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql triggers l.jpg

SQL Triggers

Jeffrey P. Landry

University of South Alabama

University of South Alabama School of CIS


Triggers l.jpg
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


Trigger firing l.jpg
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


Trigger actions l.jpg
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


Sql server syntax for creating a trigger l.jpg
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


Triggers vs stored procedures l.jpg
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


Some roles triggers play l.jpg
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


Inserted and deleted tables l.jpg
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


Example audit log l.jpg
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


More examples l.jpg
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


Examples with business rules l.jpg
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


ad