Cas d'utilisation des déclencheurs AFTER (DML)

Création et optimisation de déclencheurs dans SQL Server

Florin Angelescu

Instructor

Conserver l'historique des modifications de lignes

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 |

Conserver l'historique des modifications de lignes

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 |

Conserver l'historique des modifications de lignes

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 |

Conserver l'historique des modifications de lignes

CREATE TRIGGER CopyCustomersToHistory
ON Customers

AFTER INSERT, UPDATE

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

Vérification des tables avec des déclencheurs

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();

Aviser les utilisateurs

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

Passons à la pratique !

Création et optimisation de déclencheurs dans SQL Server