Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you priortize multiple triggers of a table?

I have a couple of triggers on a table that I want to keep separate and would like to priortize them.

I could have just one trigger and do the logic there, but I was wondering if there was an easier/logical way of accomplishing this of having it in a pre-defined order ?

like image 204
Brian Liang Avatar asked Sep 18 '08 17:09

Brian Liang


People also ask

Can we have multiple triggers in the same table?

You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created.

Can we have multiple triggers in the same table in SQL Server?

SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers. We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.

What is the order of triggers?

What is trigger order? Trigger order is a pre-set order, that users place ahead with an order price and contracts amount (like a limit order), which will only be triggered under specific conditions (a trigger price/trigger). Once the latest traded price has reached the "trigger", the pre-set order will be executed.

Which trigger will fire first?

Constraint are always checked first in AFTER trigger, whereas BEFORE trigger fires first.


1 Answers

Use sp_settriggerorder. You can specify the first and last trigger to fire depending on the operation.

sp_settriggerorder on MSDN

From the above link:
A. Setting the firing order for a DML trigger
The following example specifies that trigger uSalesOrderHeader be the first trigger to fire after an UPDATE operation occurs on the Sales.SalesOrderHeader table.

USE AdventureWorks;
GO
sp_settriggerorder 
    @triggername= 'Sales.uSalesOrderHeader', 
    @order='First', 
    @stmttype = 'UPDATE';

B. Setting the firing order for a DDL trigger
The following example specifies that trigger ddlDatabaseTriggerLog be the first trigger to fire after an ALTER_TABLE event occurs in the AdventureWorks database.

USE AdventureWorks;
GO
sp_settriggerorder 
    @triggername= 'ddlDatabaseTriggerLog', 
    @order='First', 
    @stmttype = 'ALTER_TABLE', 
    @namespace = 'DATABASE';
like image 120
Scott Nichols Avatar answered Nov 22 '22 13:11

Scott Nichols