Dépannage des déclencheurs

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

Florin Angelescu

Instructor

Suivre l'exécution des déclencheurs (vue système)

SELECT * FROM sys.dm_exec_trigger_stats;

| database_id | object_id  | last_execution_time     | execution_count |...|
|-------------|------------|-------------------------|-----------------|...|
| 4           | 1429580131 | 2019-05-26 18:30:12.660 | 1               |...|
| 4           | 1445580188 | 2019-05-26 18:30:26.450 | 2               |...|

Suivre l'exécution des déclencheurs (solution personnalisée)

CREATE TRIGGER PreventOrdersUpdate
ON Orders
INSTEAD OF UPDATE
AS
    RAISERROR ('Updates on "Orders" table are not permitted.
                Place a new order to add new products.', 16, 1);

Suivre l'exécution des déclencheurs (solution personnalisée)

ALTER TRIGGER PreventOrdersUpdate
ON Orders
INSTEAD OF UPDATE
AS
    INSERT INTO TriggerAudit (TriggerName, ExecutionDate)
    SELECT 'PreventOrdersUpdate', 
           GETDATE();

    RAISERROR ('Updates on "Orders" table are not permitted.
                Place a new order to add new products.', 16, 1);

Suivre l'exécution des déclencheurs (solution personnalisée)

UPDATE Orders
SET Quantity = 300
WHERE OrderID = 784;

(1 row affected)
Msg 50000, Level 16, State 1, Procedure PreventOrdersUpdate, Line 11
                                                [Batch Start Line 27]
Updates on "Orders" table are not permitted.
Place a new order to add new products.

(0 rows affected)

Suivre l'exécution des déclencheurs (solution personnalisée)

SELECT * FROM TriggerAudit;

| ID | TriggerName         | ExecutionDate           |
|----|---------------------|-------------------------|
| 1  | PreventOrdersUpdate | 2019-05-26 19:28:04.483 |

Repérer les déclencheurs liés à une table

SELECT name AS TableName,
       object_id AS TableID
FROM sys.objects
WHERE name = 'Products';

| TableName | TableID   |
|-----------|-----------|
| Products  | 917578307 |

Repérer les déclencheurs liés à une table

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
WHERE o.name = 'Products';

Repérer les déclencheurs liés à une table

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
WHERE o.name = 'Products';

| TableName | TableID   | TriggerName           | TriggerID  | IsDisabled | IsInsteadOf |
| Products  | 917578307 | TrackRetiredProducts  | 1349579846 | 0          | 0           |
| Products  | 917578307 | ProductsNewItems      | 1397580017 | 0          | 0           |
| Products  | 917578307 | PreventProductChanges | 1541580530 | 0          | 1           |

Identifier les événements qui déclenchent un déclencheur

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf,
       te.type_desc AS FiringEvent
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
WHERE o.name = 'Products';

Identifier les événements qui déclenchent un déclencheur

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf,
       te.type_desc AS FiringEvent
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
WHERE o.name = 'Products';

| TableName | TableID   | TriggerName           | TriggerID  | IsDisabled | IsInsteadOf | FiringEvent |
| Products  | 917578307 | TrackRetiredProducts  | 1349579846 | 0          | 0           | DELETE      |
| Products  | 917578307 | ProductsNewItems      | 1397580017 | 0          | 0           | INSERT      |
| Products  | 917578307 | PreventProductChanges | 1541580530 | 0          | 1           | UPDATE      |

Afficher les définitions des déclencheurs

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf,
       te.type_desc AS FiringEvent,
       OBJECT_DEFINITION(t.object_id) AS TriggerDefinition
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
WHERE o.name = 'Products';

Afficher les définitions des déclencheurs

SELECT o.name AS TableName,
       o.object_id AS TableID,
       t.name AS TriggerName,
       t.object_id AS TriggerID,
       t.is_disabled AS IsDisabled,
       t.is_instead_of_trigger AS IsInsteadOf,
       te.type_desc AS FiringEvent,
       OBJECT_DEFINITION(t.object_id) AS TriggerDefinition
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
WHERE o.name = 'Products';

| TableName | TableID   | TriggerName           |...| FiringEvent | TriggerDefinition |
| Products  | 917578307 | TrackRetiredProducts  |...| DELETE      | CREATE TRIGGER TrackRetiredProducts ON Produc... |
| Products  | 917578307 | ProductsNewItems      |...| INSERT      | CREATE TRIGGER ProductsNewItems ON Products A... |
| Products  | 917578307 | PreventProductChanges |...| UPDATE      | CREATE TRIGGER PreventProductChanges ON Produ... |

Passons à la pratique !

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