The Microsoft SQL database server supports the use of Triggers on its tables. Triggers are a special kind of stored procedure that can be executed when records in a table are either inserted, updated, or deleted.
Continue Reading…
The Firebird database engine supports the use of Triggers on its tables. Triggers are basically a set of SQL scripts that can be executed when data in a table is either inserted, updated, or deleted.
Sometimes you may want to temporarily disable a trigger without completely removing it. The steps to do this, and enable the trigger again, are outlined below.
Disable Trigger:
ALTER TRIGGER <trigger name> INACTIVE;
Enable Trigger:
ALTER TRIGGER <trigger name> ACTIVE;
Replace <trigger name> with the name of the trigger you wish to disable or enable.
Microsoft SQL Server allows you to add multiple triggers on a specific table. This is a great feature of Microsoft SQL Server, however one of the issues that can catch people out is in the order that the triggers will ‘fire’. By default, the triggers will run in the order that they were created/added to the database (typically, but not guaranteed). SQL Server does provide a way to set the order of ‘AFTER’ triggers (to an extent), but it must be done using T-SQL statements as opposed to using GUI tools in Management Studio.
Microsoft SQL Server only allows you to set which triggers fires first, and which fires last. This is great if you only have 3 or fewer triggers on a table, but if you have more than 3, you will end up with 2 or more in the middle part, which will end up always running in an undefined order (typically in the order that they were created, but there are no guarantees).
The ‘sp_settriggerorder’ stored procedure allows the setting of the trigger order.
The syntax for this stored procedure is:
sp_settriggerorder 'trigger_name', 'order', 'trigger_type'
| Parameter | Description |
| trigger_name | The name of the trigger that you want to set the order for. |
| order | The order that you want to set it as. The valid options are ‘first’ or ‘last’ (or ‘none’ to clear the ordering). |
| trigger_type | The statement type of trigger, eg ‘insert’, ‘update’, or ‘delete’. |
An example of setting the ‘auditCustomers’ trigger to fire first:
sp_settriggerorder 'auditCustomers', 'first', 'insert'
An example of setting the ‘CopyAddressToInvoice’ trigger to fire last:
sp_settriggerorder 'CopyAddressToInvoice', 'last', 'insert'
Keep in mind that if you ever drop and recreate a trigger, the ordering information will get deleted, and you will need to run the ‘sp_settriggerorder’ statement again.
Ever wanted to know what triggers exist on a database? The script below will produce a list of all the triggers on the current database, and also what table they are linked to.
SELECT Name AS TriggerName, OBJECT_NAME(parent_object_id) AS TableName FROM sys.objects WHERE type='TR'