Introduction to Triggers

• What is a trigger?

  • Special type of stored procedure
  • Executed when an event occurs in the database server

• Types of Trigger(based on T-SQL commands)

  • Data Manipulation Language (DML) triggers
    • INSERT, UPDATE or DELETE statements
  • Data Definition Language (DDL) triggers
    • CREATE, ALTER or DROP statements
  • Logon triggers
    • LOGON events

• Types of Trigger(based on behavior)

  • AFTER trigger
    • The original statment executes
    • Additional statements are triggered
    • Examples of use cases:
      • Rebuild an index after a large insert
      • Notify the admin when data is updated
  • INSTEAD OF trigger
    • The original statement is prevented from execution
    • A replacement statement is executed instead
    • Examples of use cases:
      • Prevent insertions
      • Prevent updates
      • Prevent deletions
      • Prevent object modifications
      • Notify the admin

• Trigger definition (with AFTER)

        -- Create the trigger by giving it a descriptive name
        CREATE TRIGGER ProductsTrigger
        -- The trigger needs to be attached to a table
        ON Products
        -- The trigger behavior type
        AFTER INSERT
        -- THe beginning of the trigger workflow
        AS
        -- The action executed by the trigger
        PRINT('An insert of data was made in the Products table.');
  

• Trigger definition(with ISTEAD OF)

        -- Create the trigger by giving it a descriptive name
        CREATE TRIGGER PreventDeleteFromOrders
        -- The trigger needs to be attached on a table
        ON Orders
        -- The trigger behavior type
        INSTEAD OF DELETE
        -- The beginning of the trigger workflow
        AS
        -- The action executed by the trigger
        PRINT('You are not allowed to delete rows from the Orders table.');
  

• AFTER vs. INSTEAD OF

        CREATE TRIGGER MyFirstAfterTrigger
        ON Table1
        -- Triggered after
        -- the firing event (UPDATE
        AFTER UPDATE
        AS 
        {trigger_actions_section};
  

        CREATE TRIGGER MyFirstInsteadOfTrigger
        ON Table2
        -- Triggered instead of
        -- the firing event (UPDATE)
        INSTEAD OF UPDATE
        AS
        {trigger_actions_section};
  

</hr>

How DML triggers are used

• Why should we use DML triggers?

  • Initiating actions when manipulating data
  • Preventing data manipulation
  • Tracking data or database object changes
  • User auditing and database security

• Deciding between AFTER and INSTEAD Of

  AFTER trigger	INSTEAD OF trigger
  Initial event fires the trigger	Initial event fires the trigger
  Initial event executes	Initial event is not executed anymore
  The trigger actions execute	The trigger actions execute

• AFTER trigger usage example

        CREATE TRIGGER SalesNewInfoTrigger
        ON Sales
        AFTER INSERT
        AS
        EXEC sp_cleansing @Tabel = 'Sales';
        EXEC sp_generateSalesReport;
        EXEC sp_sendnotification;
  

  • Data is inserted into a sales table
  • Start a data cleansing procedure
  • Generate a table report with the procedure
  • Notify the database administrator

• INSTEAD OF trigger usage example

        CREATE TRIGGER BulbsStockTrigger
        ON Bulbs
        INSTEAD OF INSERT
        AS
        IF EXISTS (SELECT * FROM Bulbs AS b
        INNER JOIN inserted AS i
                    ON b.Brand = i.Brand
                    AND b.Model = i.Model
        WHERE b.Stock = 0)
        BEGIN
            UPDATE b
            SET b.Power = i.Power,
                b.Stock = i.Stock
            FROM Bulbs AS b
            INNER JOIN inserted AS i
                        ON b.Brand = i.Brand
                            AND b.Model = i.Modle
            WHERE b.Stock = 0
        END
        ELSE
            INSERT INTO Bulbs
            SELECT * FROM inserted;
  

  • The power changes for some models
  • Update only the products with no stock
  • Add new rows for the products with stock

---

Trigger Alternatives

• Triggers vs Stored Procedures

  Triggers	Stored procedures
  Fired automatically by an event	Run only when called explicitly
  sql INSERT INTO Orders [...];	sql EXECUTE sp_DailyMaintenance;
  Don’t allow parameters or transactions	Accept input parameters and transactions
  Cannot return values as output	Can return values as output
  Used for: 1. auditing 2. integrity enforcement	Used for: 1. general tasks 2. user-specific needs

• Triggers vs computed columns

  Triggers	Computed Columns
  calculate column values	calculate column values
  use columns from other tables for calculations	use columns only from the same table for calculations
  INSERT or UPDATE used to calculate	calculatino defined when creating the table
  sql [...] UPDATE SET TotalAmount = Price * Quantity [...	sql [...] TotalAmount AS Price * Quantity [...]

• Example of a computed column

        CREATE TABLE [SalesWithPrice]
        (
            [OrderID] INT IDENTITY(1,1),
            [Customer] NVARCHAR(50),
            [Product] NVARCHAR(50),
            [Price] DECIMAL(10,2),
            [Currency] NVARCHAR(3),
            [Quantity] INT,
            [OrderDate] DATE DEFAULT (GETDATE()),
            [TotalAmount] AS [Quantity] * [Price]
        )
  

• Using a trigger as a computed column

        CREATE TRIGGER [SalesCalculateTotalAmount]
        ON [SalesWithoutPrice]
        AFTER INSERT
        AS
            UPDATE [sp]
            SET [sp].[TotalAmount] = [sp].[Quantity] * [p].[Price]
            FROM [SalesWithoutPrice] AS [sp]
            INNER JOIN [Products] AS [p] ON [sp].Product = [p].[Product]
            WHERE [sp].[TotalAmount] IS  NULL;