Gestion des déclencheurs

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

Florin Angelescu

Instructor

Obtenir de l'info à partir de sys.triggers

SELECT * FROM sys.triggers;

| name                   | parent_class_desc | create_date | is_disabled | is_instead_of_trigger |
|----------------------|-------------------|-------------|---------------|-----------------------|
| TrackRetiredProducts   | OBJECT_OR_COLUMN  | 2019-04-19  | 0           | 0                     |
| PreventOrdersUpdate    | OBJECT_OR_COLUMN  | 2019-04-22  | 0           | 1                     |
| TrackDatabaseTables    | DATABASE          | 2019-04-23  | 0           | 0                     |
| KeepCanceledOrders     | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 0                     |
| DiscountsPreventDelete | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventNewDiscounts    | OBJECT_OR_COLUMN  | 2019-04-27  | 0           | 1                     |
| PreventTableDeletion   | DATABASE          | 2019-04-27  | 0           | 0                     |

Obtenir de l'info à partir de sys.triggers

name

object_id

parent_class

parent_class_desc

parent_id

nom du déclencheur

identifiant unique du déclencheur

type de déclencheur en entier

type de déclencheur en texte

identifiant unique de l'objet parent

Obtenir de l'info à partir de sys.triggers

create_date

modify_date

is_disabled

is_instead_of_trigger

date de création

date de dernière modification

état actuel

déclencheur INSTEAD OF ou AFTER

Obtenir de l'info à partir de sys.server_triggers

SELECT * FROM sys.server_triggers;

| name                 | parent_class_desc | create_date | is_disabled |...|
|----------------------|-------------------|-------------|-------------|---|
| DenyNewDatabases     | SERVER            | 2019-01-22  | 0           |...|
| DenyNewLinkedServers | SERVER            | 2019-01-22  | 1           |...|
| DenyDatabaseDelete   | SERVER            | 2019-01-22  | 1           |...|

Obtenir de l'info à partir de sys.trigger_events

SELECT * FROM sys.trigger_events;

| object_id  | type | type_desc       | event_group_type | event_group_type_desc |
|------------|------|-----------------|------------------|-----------------------|
| 1285579618 | 24   | CREATE_INDEX    | NULL             | NULL                  |
| 1301579675 | 52   | ALTER_PROCEDURE | NULL             | NULL                  |
| 1301579675 | 53   | DROP_PROCEDURE  | NULL             | NULL                  |
| 1333579789 | 1    | INSERT          | NULL             | NULL                  |
| 1365579903 | 2    | UPDATE          | NULL             | NULL                  |
| 1381579960 | 3    | DELETE          | NULL             | NULL                  |
| 1557580587 | 21   | CREATE_TABLE    | 10017            | DDL_TABLE_VIEW_EVENTS |
| 1557580587 | 22   | ALTER_TABLE     | 10017            | DDL_TABLE_VIEW_EVENTS |
| 1557580587 | 23   | DROP_TABLE      | 10017            | DDL_TABLE_VIEW_EVENTS |

Obtenir de l'info à partir de sys.trigger_events

object_id

type

type_desc

event_group_type

event_group_type_desc

identifiant unique du déclencheur

type d'événement en entier

type d'événement en texte

type de groupe d'événements en entier

type de groupe d'événements en texte

Obtenir de l'info à partir de sys.trigger_events

DDL_TABLE_VIEW_EVENTS

CREATE_TABLE DROP_TABLE ALTER_TABLE

CREATE_VIEW DROP_VIEW ALTER_VIEW

CREATE_INDEX DROP_INDEX ALTER_INDEX

CREATE_STATISTICS DROP_STATISTICS

UPDATE_STATISTICS CREATE_XML_INDEX

[...]

Obtenir de l'info à partir de sys.server_trigger_events

SELECT * FROM sys.server_trigger_events;

| object_id  | type | type_desc              | event_group_type | event_group_type_desc |
|------------|------|------------------------|------------------|-----------------------|
| 1285579618 | 24   | CREATE_LINKED_SERVER   | NULL             | NULL                  |
| 1301579675 | 52   | DROP_DATABASE          | 10004            | DDL_DATABASE_EVENTS   |

Obtenir de l'info à partir de sys.trigger_event_types

SELECT * FROM sys.trigger_event_types;

| type  | type_name        | parent_type |
|-------|------------------|-------------|
| 21    | CREATE_TABLE     | 10018       |
| 22    | ALTER_TABLE      | 10018       |
| 23    | DROP_TABLE       | 10018       |
| 24    | CREATE_INDEX     | 10020       |
| 25    | ALTER_INDEX      | 10020       |
| 26    | DROP_INDEX       | 10020       |
| 10018 | DDL_TABLE_EVENTS | 10017       |

Gestion des déclencheurs en pratique

SELECT t.name AS TriggerName,
       t.parent_class_desc AS TriggerType,
       te.type_desc AS EventName,
       o.name AS AttachedTo,
       o.type_desc AS ObjectType
FROM sys.triggers AS t
INNER JOIN sys.trigger_events AS te ON te.object_id = t.object_id
LEFT OUTER JOIN sys.objects AS o ON o.object_id = t.parent_id;

Gestion des déclencheurs en pratique

| TriggerName               | TriggerType      | EventName       | AttachedTo | ObjectType |
|---------------------------|------------------|-----------------|------------|------------|
| IndexSecurityTrigger      | DATABASE         | CREATE_INDEX    | NULL       | NULL       |
| ProcedureSecurityTrigger  | DATABASE         | ALTER_PROCEDURE | NULL       | NULL       |
| ProcedureSecurityTrigger  | DATABASE         | DROP_PROCEDURE  | NULL       | NULL       |
| CustomerDiscountHistory   | OBJECT_OR_COLUMN | UPDATE          | Discounts  | USER_TABLE |
| TrackRetiredProducts      | OBJECT_OR_COLUMN | DELETE          | Products   | USER_TABLE |
| PreventOrdersUpdate       | OBJECT_OR_COLUMN | UPDATE          | Orders     | USER_TABLE |
| PreventDiscountsDelete    | OBJECT_OR_COLUMN | DELETE          | Discounts  | USER_TABLE |

Passons à la pratique !

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