Triggers are a powerful device that can be used to enforce the business rules automatically when information is modified. Triggers can also be used to maintain information integrity. But they are to maintain the integrity of the information. Triggers should be used to maintain the integrity of the information only if you are unable to enforce information integrity through restrictions, rules & defaults. CALL can not be created on temporary tables.
When to Use Triggers
A trigger is a database object that is attached to a table. In lots of respects is similar to a stored procedure. As a matter of fact, triggers are often called a sort of "special stored procedure." The main difference between a trigger & a stored procedure is that the former is attached to a table & is only triggered when an INSERT, UPDATE or DELETE occurs. You specify the manipulation action (s) that fires the trigger when it is created. There are lots of reasons to use triggers. If you have a table that keeps track of messages, you may need to have a copy of them mailed to you if they are urgent. If there's triggers that would have some solutions, although not as elegant. You could alter the application (s) for registration of the messages. This means you may be redundant encoding the same in every application that logs messages.
Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, INSERT, DELETE or amendment to receive a trigger for a specific class of information manipulation actions. When FOR UPDATE is specified, the IF UPDATE (column name) clause can be used to fight a shot to updates affecting a particular column’s Server 2000 greatly improves trigger functionality, extending the capabilities of the triggers they already know & love, & the addition of a new type of trigger, the "instead of" trigger.
SQL Server 2000 has lots of types of triggers:
1. after Trigger
2. after multiple triggers
3. In lieu of triggers
4. Mixing Type Triggers