Trigger Management and Optimization

In the previous part I’ve talked about how to properly use Triggers (AFTER, INSTEAD OF) along with their use cases and limitations.

In this part, I’ll talk about:

1. Trigger Modifications
2. Trigger Management and Tracking Triggers Executions.

Trigger Modifications

Because triggers are objects we can deal with them as we deal with any DB object by deleting or creating and so on.

1. Deleting A trigger on a table or view
2. Disabling a trigger

Deleting triggers

Deleting table and view triggers

DROP TRIGGER PreventNewDiscounts;

Deleting database-level triggers

DROP TRIGGER PreventViewsModifications
ON DATABASE;

Deleting server triggers

DROP TRIGGER DisallowLinkedServers
ON ALL SERVER;

Disabling triggers

There are some cases when you need just to stop a trigger for a specific period of time AKA **Disabling” a trigger.

A deleted trigger can never be used again unless you recreate the trigger.

📘 note

When you need to disable a trigger, you need to explicitly specify the object the trigger is attached to, even if it is a normal table.

DISABLE TRIGGER PreventNewDiscounts
ON Discount

DISABLE TRIGGER PreventViewsModifications
ON DATABASE

DISABLE TRIGGER DisallowLinkedServers
ON ALL SERVER

Re-enabling triggers

ENABLE TRIGGER PreventViewsModifications
ON DATABASE

Altering triggers

there are two main approaches to changing triggers after they were created

1. Create and Drop workflow
2. using ALTER

In the first approach, you’re going to create the trigger and if something happened and you wish to change it, you DROP the trigger and re-create it. which is something frustrating during development, instead you can ALTER the trigger in time

ALTER TRIGGER X
ON Y
INSTEAD OF DELETE
AS
    -- Your changes

Trigger Management

To get information about the current triggers you’ve on your server, we’ll explore the sys.triggers table which contains information about the system triggers

SELECT * FROM sys.triggers

this table contains about 13 attributes, but we are going to explore the most important ones.

If you want to know the server level triggers

SELECT * FROM sys.server_triggers

the table will have the same structure as the database triggers level with the same information

What if you need to identify the events that will fire a trigger?

this information is stored in sys.trigger_events

you don’t need to memorize all of the events that will fire the triggers, they are contained in sys.trigger_event_types

the problem here is that the information is divided into many tables, if you want to form a good answer

“list the trigger along with their firing events and object they’re attatched to” we need to join the tables together

SELECT t.name as TriggerName,
       t.parent_class_desc AS TriggerType,
       te.type_desc AS EventName,
       o.name As AttatchedTo,
       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;

📘 note:

the second join is chosen to be a LEFT join because database-level triggers do not appear as attached to an object.

In real-world you’ll not use those views in isolation, they usually combined together to get a useful information

Practice Time

-- Get the disabled triggers
SELECT name,
    object_id,
  parent_class_desc
FROM sys.triggers
WHERE is_disabled = 1;

-- Check for unchanged server triggers
SELECT *
FROM sys.server_triggers
WHERE modify_date = create_date;

-- Get the database triggers
SELECT *
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE';

-- counting AFTER triggers
SELECT COUNT(object_id) FROM
sys.triggers
WHERE is_instead_of_trigger = 'false'

troubleshooting Triggers

• Keep a history of triggers runs
• how to search for triggers causing issues

Tracking Trigger Exectuins (system views)

one important thing to keep in mind when troubleshooting triggers is to have a history of their execution

note: SQL Server provides information on the execution of the triggers that are currently stored in memory, so when the triggers are removed from memory they are removed from the view as well sys.dm_exec_trigger_stats

so how to get around this problem? by creating our custom solution

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)

UPDATE Orders
SET Quanity = 400
WHERE ID = 600

This will raise an error, but also we got a permanent record that we can use to track the history of triggers runs

SELECT * FROM TriggerAudit

How can we identify the triggers on a certain table or view? using sys.objects table which contains information about the objects on the database.

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

Then

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'

To identify the events capable of firing a trigger, we’ll join to the sys.trigger_events also

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
INNER JOIN sys.trigger_events AS te on t.object_id = te.object_id
WHERE o.name ='Products'

if we want to further view also the trigger definition, we’ll use the OBJECT_DEFINITION() method which returns the definition for an object Id passed as an argument

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

now you can inspect and modify the trigger definition if needed

Practice Time

-- Get the table ID
SELECT object_id AS TableID
FROM sys.objects
WHERE name = 'Orders';

-- Get the trigger name
SELECT t.name AS TriggerName
FROM sys.objects AS o
-- Join with the triggers table
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
WHERE o.name = 'Orders';

SELECT t.name AS TriggerName
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
-- Get the trigger events
INNER JOIN sys.trigger_events AS te ON te.object_id = t.object_id
WHERE o.name = 'Orders'
-- Filter for triggers reacting to new rows
AND te.type_desc = 'UPDATE';

SELECT t.name AS TriggerName
FROM sys.objects AS o
INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
-- Get the trigger events
INNER JOIN sys.trigger_events AS te ON te.object_id = t.object_id
WHERE o.name = 'Orders'
-- Filter for triggers reacting to new rows
AND te.type_desc = 'UPDATE';

Resources

• Datacamp: Building and Optimizing Triggers