MS-Triggers

Document Sample
MS-Triggers Powered By Docstoc
					     Module 11:
Implementing Triggers
Overview


    Introduction
    Defining
        Create, drop, alter triggers
    How Triggers Work
    Examples
    Performance Considerations
        Analyze performance issues related to triggers
 Introduction to Triggers


     What Is a Trigger?
     Uses
     Considerations for Using Triggers
What Is a Trigger?


     Associated with a Table
     Invoked Automatically
     Cannot Be Called Directly
     Is Part of a Transaction
         Along with the statement that calls the trigger
         Can ROLLBACK transactions (use with care)
Uses of Triggers


     Cascade Changes Through Related Tables in
      a Database
          A delete or update trigger can cascade changes to related tables:
           Soda name change to change in soda name in Sells table
     Enforce More Complex Data Integrity Than a
      CHECK Constraint
          Change prices in case of price rip-offs.
     Define Custom Error Messages
     Maintain Denormalized Data
          Automatically update redundant data.
     Compare Before and After States of Data Under Modification
Considerations for Using Triggers


     Triggers Are Reactive; Constraints Are Proactive
     Constraints Are Checked First
     Tables Can Have Multiple Triggers for Any Action
     Table Owners Can Designate the First and Last Trigger
      to Fire
     You Must Have Permission to Perform All Statements
      That Define Triggers
     Table Owners Cannot Create AFTER Triggers on Views
      or Temporary Tables
 Defining Triggers


     Creating Triggers
     Altering and Dropping Triggers
 Creating Triggers


       Requires Appropriate Permissions
       Cannot Contain Certain Statements

Use Northwind
GO
CREATE TRIGGER Empl_Delete ON Employees
FOR DELETE
AS
IF (SELECT COUNT(*) FROM Deleted) > 1
BEGIN
   RAISERROR(
      'You cannot delete more than one employee at a time.', 16, 1)
   ROLLBACK TRANSACTION
END
 Altering and Dropping Triggers

       Altering a Trigger
           Changes the definition without dropping the trigger
           Can disable or enable a trigger
USE Northwind
GO
ALTER TRIGGER Empl_Delete ON Employees
FOR DELETE
AS
IF (SELECT COUNT(*) FROM Deleted) > 6
BEGIN
   RAISERROR(
      'You cannot delete more than six employees at a time.', 16, 1)
   ROLLBACK TRANSACTION
END

       Dropping a Trigger
 How Triggers Work


     How an INSERT Trigger Works
     How a DELETE Trigger Works
     How an UPDATE Trigger Works
     How an INSTEAD OF Trigger Works
     How Nested Triggers Work
     Recursive Triggers
How an INSERT Trigger Works
 INSERT statement to a table with an INSERT Trigger Defined
   TRIGGER Actions Execute
      INSERT [Order Details] VALUES
                  Trigger Code:
      (10525, 2, 19.00, 5, 0.2)
                  USE Northwind
      1 INSERT Statement to a Table with an INSERT
                  CREATE TRIGGER OrdDet_Insert
         Trigger Defined Details
                  ON [Order Details]
                   Order
                  FOR INSERT
                  AS
                   OrderID ProductID UnitPrice Quantity Discount
      2           UPDATE P SET
         INSERT Statement Logged 7
                   10522 10           31.00               0.2
                  UnitsInStock = (P.UnitsInStock – I.Quantity)
                   10523 41                       9       0.15
                  FROM Products 9.65 P INNER JOIN Inserted AS I
                                       AS
      3           ON P.ProductID = I.ProductID
                   10524       7      30.00      24       0.0
           Trigger Actions Executed
 Order Details
                           10523      2         19.00          0.2
                                                         5 Products
OrderID ProductID UnitPrice Quantity Discount
                                                              ProductID UnitsInStock … …
 10522 10       31.00  7    0.2
 10523 41       9.65   9    0.15                                 1         15
Insert statement logged
 10524    7     30.00 24    0.0                                  2         10
                                                                           15
       inserted                                                  3         65
 10523    2     19.00  5     0.2
       10523     2    19.00      5                      0.2      4         20
How a DELETE Trigger Works
DELETE Statement to a table with a DELETE Trigger Defined
Trigger Actions Execute
                          Categories
      1               CategoryID a Table with Description
          DELETE Statement toCategoryName a DELETE                  Picture
 DELETE   Categories      1      Beverages Products coffees… 0x15…
                                             Soft drinks,
 WHERE    Statement Defined                ProductID Discontinued … …
                          2 Condiments     Sweet and savory … 0x15…
 CategoryID = 4
     2                    3 Confections     1          0
                                           Desserts, candies, … 0x15…
        DELETE             Logged
                    Statement
                          4                 2
                                            2
                            Dairy Products Cheeses 0   1        0x15…
 USE Northwind
                                            3          0
 CREATE TRIGGER Category_Delete
     3
        Trigger Actions Executed
    ON Categories                           4          0
      FOR DELETE
 AS
      UPDATE P SET Discontinued = 1
       FROM Products AS P INNER JOIN deleted AS d
          P.CategoryID = d.CategoryID
       ON DELETE statement logged

                 Deleted
                  4    Dairy Products Cheeses   0x15…
How an UPDATE Trigger Works
 TRIGGER Actions Execute with an UPDATE Trigger Defined
 UPDATE Statement to a table
USE Northwind
GO UPDATE Employees
   SET TRIGGER Employee_Update Table with an UPDATE
CREATE EmployeeID = 17
     1 UPDATE Statement to a
 ON Employees
   WHERE EmployeeID = 2
        Trigger Defined
 FOR UPDATE
AS                     Employees
AS
IF UPDATE (EmployeeID)
IF UPDATE (EmployeeID)EmployeeID LastName FirstName    Title HireDate
BEGIN TRANSACTION
BEGIN TRANSACTION
     2 UPDATE Statement
 RAISERROR ('Transaction 1 Logged as INSERT and
 RAISERROR ('Transaction cannot be processed.\
                                 Davolio   Nancy Sales Rep. ~~~
                           cannot be processed.\
 ***** Employee ID number cannot be modified.', 10, 1)
 ***** Employee ID number cannot be modified.', 10, 1)
        DELETE Statements Barr
 ROLLBACK TRANSACTION
 ROLLBACK TRANSACTION     2      Fuller    Andrew R Pres. ~~~
                                                    Vice
                                   3        Leverling   Janet Sales Rep.         ~~~
  Transaction cannot be processed. 4        Peacock     Margaret Sales Rep.      ~~~
     3    Trigger Actions Executed
  ***** Member number cannot be modified
                          Employees
    UPDATE Statement logged as INSERT and DELETE Statements
                                       EmployeeID LastName FirstName     Title         HireDate
                      inserted
                                           1     Davolio   Nancy Sales Rep.             ~~~
                        17   Fuller         Andrew Vice Pres.   ~~~
                                           2     Barr
                                                 Fuller    Andrew R Pres.
                                                                   Vice                 ~~~
                      deleted              3     Leverling Janet Sales Rep.             ~~~
                       2      Fuller             Peacock Margaret Sales Rep.
                                           4Andrew Vice Pres.   ~~~                     ~~~
How an INSTEAD OF Trigger Works
 Create a View That Combines Two or More Tables
  CREATE is Made
 UPDATE VIEW
  Customers
 to the View AS OF Trigger Can Be on a Table or View
     1 INSTEAD
   SELECT *
    FROM CustomersMex
                      Customers
   UNION
 INSTEAD OF
   SELECT *           CustomerID CompanyName Country Phone   …
 trigger directs the
                        Initiates the Trigger Does NOT Occur
 updateThe Action ThatALFKI Alfreds Fu… Germany 030-0074321 ~~~
     2   CustomersGer
    FROM to the base    ALFKI
 table                  ANATR    Ana Trujill… Mexico   (5) 555-4729    ~~~
                        ANTON    Antonio M… Mexico     (5) 555-3932    ~~~
 Original Insert to
CustomersMex Updates to Views Not Previously Updateable
     3 Allows
 the Customers
CustomerID CompanyName Country
 View Does Not           CustomersGerPhone     …
 Occur Ana Trujill… Mexico (5) 555-4729 ~~~
 ANATR                   CustomerID CompanyName Country Phone …
 ANTON   Antonio M… Mexico (5) 555-3932 ~~~
                         ALFKI Alfreds Fu… Germany 030-0074321        ~~~
 CENTC   Centro Co… Mexico (5) 555-3392 ~~~
                        BLAUS Blauer Se… Germany 0621-08460           ~~~
                        DRACD Drachenb… Germany 0241-039123           ~~~
How Nested Triggers Work
                   Order_Details
 OrDe_Update
                  OrderID ProductID UnitPrice Quantity Discount
                   10522     10        31.00    7       0.2
                   10523     41        9.65     9       0.15
                   10524     7         30.00    24      0.0
                   10525     2         19.00    5        0.2

                                                      Placing an order causes the
                                                      OrDe_Update trigger to
                   Products                           execute
InStock_Update
                  ProductID UnitsInStock … …
                      1           15                  Executes an UPDATE
                                                      statement on the Products
                      2           10
                                  15                  table
                      3           65
                      4           20
                                                      InStock_Update trigger
                                                      executes
         UnitsInStock + UnitsOnOrder                  Sends message
       is < ReorderLevel for ProductID 2
Recursive Triggers


     Activating a Trigger Recursively
     Types of Recursive Triggers
         Direct recursion occurs when a trigger fires and performs
          an action that causes the same trigger to fire again
         Indirect recursion occurs when a trigger fires and
          performs an action that causes a trigger on another table
          to fire
     Determining Whether to Use Recursive Triggers
 Examples of Triggers


     Enforcing Data Integrity
     Enforcing Business Rules
Enforcing Data Integrity

CREATE TRIGGER BackOrderList_Delete
    ON Products FOR UPDATE
AS
IF (SELECT BO.ProductID FROM BackOrders AS BO JOIN
    Inserted AS I ON BO.ProductID = I.Product_ID
    ) > 0
BEGIN
    DELETE BO FROM BackOrders AS BO
    INNER JOIN Inserted AS I
    ON BO.ProductID = I.ProductID
END

 Products                                            BackOrders
 ProductID UnitsInStock … …                          ProductID UnitsOnOrder   …
    1        15                                        1           15
    2        10
             15           Updated                      12          10
    3        65                                        3           65
    4        20                Trigger Deletes Row      2          15
Enforcing Business Rules
       Products with Outstanding Orders Cannot Be Deleted
        IF (Select Count (*)
          FROM [Order Details] INNER JOIN deleted
          ON [Order Details].ProductID = deleted.ProductID
           ) > 0
        ROLLBACK TRANSACTION

DELETE statement executed on   Trigger code                                 Transaction
Product table                  checks the Order Details                     rolled back
 Products                      table Order Details
ProductID UnitsInStock … …           OrderID ProductID UnitPrice Quantity Discount
   1         15                      10522      10        31.00     7      0.2
   2         10
              0                      10523      2         19.00     9      0.15
   3         65                      10524      41        9.65     24      0.0
   4         20                      10525      7         30.00

                                      'Transaction cannot be processed'
                                      'This product has order history'
Performance Considerations


     Triggers Work Quickly Because the Inserted and
      Deleted Tables Are in Cache
     Execution Time Is Determined by:
         Number of tables that are referenced
         Number of rows that are affected
     Actions Contained in Triggers Implicitly Are Part of
      a Transaction
Recommended Practices


   Use Triggers Only When Necessary



   Keep Trigger Definition Statements as Simple as Possible


   Include Recursion Termination Check Statements in
   Recursive Trigger Definitions


   Minimize Use of ROLLBACK Statements in Triggers
Review


    Introduction
    Defining
        Create, drop, alter triggers
    How Triggers Work
    Examples
    Performance Considerations
        Analyze performance issues related to triggers

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:8/17/2012
language:
pages:22