What are triggers? How many triggers you can have on a table?

Posted by

To view Verified answers click on the button below.

What are triggers? How many triggers you can have on a table? How to invoke a
trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an
INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for
UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and
you could create multiple triggers per each action. But in 7.0 there’s no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires
first or fires last using sp_settriggerorder

Triggers can’t be invoked on demand. They get triggered only when an associated action
(INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be
used to extend the referential integrity checks, but wherever possible, use constraints for
this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a
way, they are called post triggers. But in SQL Server 2000 you could create pre triggers
also. Search SQL Server 2000 books online for INSTEAD OF trigger.