Trigger management

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Getting info from 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                     |

Getting info from sys.triggers

name

object_id

parent_class

parent_class_desc

parent_id

trigger name

unique identifier of the trigger

trigger type as integer

trigger type as text

unique identifier of the parent object

Getting info from sys.triggers

create_date

modify_date

is_disabled

is_instead_of_trigger

date of creation

date of last modification

current state

INSTEAD OF or AFTER trigger

Getting info from 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           |...|

Getting info from 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 |

Getting info from sys.trigger_events

object_id

type

type_desc

event_group_type

event_group_type_desc

unique identifier of the trigger

event type as integer

event type as text

event group type as integer

event group type as text

Getting info from 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

[...]

Getting info from 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   |

Getting info from 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       |

Trigger management in practice

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;

Trigger management in practice

| 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 |

Let's practice!

Building and Optimizing Triggers in SQL Server