Use cases for AFTER triggers (DML)

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Keeping a history of row changes

SELECT * FROM Customers;

| Customer        | ContractID   | Address               | PhoneNo        |
|-----------------|--------------|-----------------------|----------------|
| Every Fruit     | ABF138256334 | 2522 Consectetuer St. | 1-307-717-2294 |
| eFruits         | 691C37BC3CED | 1908 Fames Street     | 1-854-241-5573 |
| Healthy Choices | 435ADE342265 | 2826 Mauris Rd.       | 1-369-765-1647 |
| Health Mag      | 73F6095C6930 | 1080 Aliquet. St.     | 1-634-676-3716 |
| Fruit Mania     | 5CC27CBC78BA | 311 In Avenue         | 1-790-501-4629 |

Keeping a history of row changes

SELECT * FROM CustomersHistory;

| Customer        | ContractID   | Address               | PhoneNo        | ChangeDate |
|-----------------|--------------|-----------------------|----------------|------------|
| Every Fruit     | ABF138256334 | 2522 Consectetuer St. | 1-307-717-2294 | 2017-05-03 |
| eFruits         | 691C37BC3CED | 1908 Fames Street     | 1-854-241-5573 | 2017-10-23 |
| Healthy Choices | 435ADE342265 | 2826 Mauris Rd.       | 1-369-765-1647 | 2018-02-10 |
| Health Mag      | 73F6095C6930 | 1080 Aliquet. St.     | 1-634-676-3716 | 2018-03-03 |
| Fruit Mania     | 5CC27CBC78BA | 311 In Avenue         | 1-790-501-4629 | 2018-09-15 |

Keeping a history of row changes

Customers

| Customer        | ContractID   | Address               | PhoneNo        |
|-----------------|--------------|-----------------------|----------------|
| eFruits         | 691C37BC3CED | 1908 Fames Street     | 1-854-241-6000 |

CustomersHistory

| Customer        | ContractID   | Address               | PhoneNo        | ChangeDate |
|-----------------|--------------|-----------------------|----------------|------------|
| eFruits         | 691C37BC3CED | 1908 Fames Street     | 1-854-241-5573 | 2017-10-23 |
| eFruits         | 691C37BC3CED | 1908 Fames Street     | 1-854-241-6000 | 2019-05-12 |

Keeping a history of row changes

CREATE TRIGGER CopyCustomersToHistory
ON Customers

AFTER INSERT, UPDATE

AS
    INSERT INTO CustomersHistory (Customer, ContractID, Address, PhoneNo)
    SELECT Customer, ContractID, Address, PhoneNo, GETDATE()
    FROM inserted;

Table auditing using triggers

CREATE TRIGGER OrdersAudit
ON Orders
AFTER INSERT, UPDATE, DELETE

AS
    DECLARE @Insert BIT = 0, @Delete BIT = 0;

    IF EXISTS (SELECT * FROM inserted) SET @Insert = 1;
    IF EXISTS (SELECT * FROM deleted) SET @Delete = 1;

    INSERT INTO [TablesAudit] ([TableName], [EventType], [UserAccount], [EventDate])
    SELECT 'Orders' AS [TableName]
           ,CASE WHEN @Insert = 1 AND @Delete = 0 THEN 'INSERT'
                 WHEN @Insert = 1 AND @Delete = 1 THEN 'UPDATE'
                 WHEN @Insert = 0 AND @Delete = 1 THEN 'DELETE'
                 END AS [Event]
           ,ORIGINAL_LOGIN()
           ,GETDATE();

Notifying users

CREATE TRIGGER NewOrderNotification
ON Orders
AFTER INSERT
AS
    EXECUTE SendNotification @RecipientEmail = '[email protected]'
                            ,@EmailSubject = 'New order placed'
                            ,@EmailBody = 'A new order was just placed.';

Let's practice!

Building and Optimizing Triggers in SQL Server