Hi Arnaud,

Thank you for the tip. However I have remarks:

1. Could you explain basically what is a trigger and in which case/situation we would like to use it?

2. Maybe give us some clues about the limits. I guess a trigger cost Sql Server resources...

Nico

What is a Database Trigger?

A database trigger is special stored procedure that is run when specific actions occur within a database. Most triggers are defined to run when changes are made to a table’s data. Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT, UPDATE, and DELETE.

Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.

The programs are called triggers since an event, such as adding a record to a table, fires their execution.

Triggers and their implementations are specific to database vendors. In this article we’ll focus on Microsoft SQL server; however, the concepts are the same or similar in Oracle and MySQL.

Events
The triggers can occur AFTER or INSTEAD OF a DML action. Triggers are associated with the database DML actions INSERT, UPDATE, and DELETE. Triggers are defined to run when these actions are executed on a specific table.

AFTER triggers
Once the DML actions, such as an INSERT completes, the AFTER trigger executes. Here are some key characteristics of AFTER triggers:

After triggers are run after a DML action, such as an INSERT statement and any ensuing referential cascade actions and constraint checks have run.
You can’t cancel the database action using an AFTER trigger. This is because the action has already completed.
One or more AFTER triggers per action can be defined on a table, but to keep things simple I recommend only defining one.
You can’t define AFTER triggers on views.

INSTEAD OF triggers
INSTEAD OF triggers, as their name implies, run in place of the DML action which caused them to fire. Items to consider when using INSTEAD OF triggers include:

An INSTEAD OF trigger overrides the triggering action. If an INSTEAD OF trigger is defined to execute on an INSERT statement, then once the INSERT statement attempt to run, control is immediately passed to the INSTEAD OF trigger.
At most, one INSTEAD OF trigger can be defined per action for a table. This makes sense, as if you had to “INSTEAD OF” triggers for an insert, which one should run?

Uses for Triggers
Here are some common uses for triggers:

Complex Auditing
You can use triggers to track changes made to tables. In our example above, changes made to the WorkOrder table are recorded a TransactionHistory table.

Typically when creating audit trails, you’ll use AFTER triggers.

You may think this is redundant, as many changes are logged in the databases journals, but the logs are meant for database recovery and aren’t easily accessible by user programs. The TransactionHistory table is easily referenced and can be incorporated into end user reports.

Enforce Business Rules
Triggers can be used to inspect all data before a DML action is performed. You can use INSTEAD OF triggers to “intercept” the pending DML operation, apply any business rules, and ultimately complete the transaction.

An example business rule may be that a customer status is defined as:

Gold – Purchases over $1,000,000 in the past 12 months.
Silver – Purchase of $500,000 to $1,000,000 in the past 12 months.
Bronze – All other purchase levels.
An INSTEAD OF trigger could be defined to check the customer status each time a customer record is added or modified. The status check would involve creating a sum of all the customers’ purchases and ensuring the new status corresponds with the sum of the last 12 months of purchases.

Derive Column Values
Triggers can be used to calculate column values. For instance, for each customer you may wish to maintain a TotalSales column on the customer record. Of course, for this to remain accurate, it would have to be update every time a sales was made.

This could be done using an AFTER trigger on INSERT, UPDATE, and DELETE statements for the Sales table.

More informations about performances
http://www.madeiradata.com/real-performance-impact-of-triggers/

Thank you Arnaud for these explanations

I just want adding one point as a warning.
I remember long time ago (SQL 2008???) wanting to use triggers on HDS table to make some calculations.
Something like that: For each power meter of the project, every time a consumption value record is inserted => trigger a script that is summing up the meter value in another table.
It worked perfectly BUT (always a but!) because I had so many meters (hence big recording flow) in the project the resources taken by all triggered actions was too high therefore SQL Server became too busy => Pending records!
So, take care before advising our customers to go on this way...

Nico

You have true, trigger function have to be fastest as possible and catch T-SQL errors