Explain Trigger

Trigger
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity
and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.

Following sql statements are not allowed in a trigger they are:-
ALL CREATE statements
ALL DROP statements
ALTER TABLE & ALTER DATABASE
TRUNCATE TABLE
GRANT AND REVOKE
UPDATE STATISTICS
RECONFIGURE
LOAD DATABASE AND LOAD TRANSACTION
ALL DISK statements
SELECT INTO

Types of Trigger
Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.

SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type

After Triggers
Triggers that run after an update, insert, or delete can be used in several ways:

* Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
* Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
* Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
Note: An AFTER trigger can be created only on tables, not on views.

Example
CREATE TRIGGER DelhiDel ON [Customers]
FOR DELETE
AS
IF (SELECT state FROM deleted) = Delhi
BEGIN
PRINT Can not remove customers from Delhi
PRINT Transaction has been canceled
ROOLBACK
END

Multiple After Triggers
More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general, you might not want to do this (it's easy to get confused if you over-use triggers), there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:

* Application based triggers (cascading deletes or validation, for example).
* Auditing triggers (for recording details of changes to critical data).

This would allow you to alter triggers of one type without fear of accidentally breaking the other.

If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the "first" or "last" to fire.

If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.

Instead Of Triggers
Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) .

Creation of Triggers
Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked.
To drop Trigger one can use DROP TRIGGER statement.

CREATE TRIGGER [owner.]trigger_name
ON[owner.] table_name
FOR[INSERT/UPDATE/DELETE] AS
IF UPDATE(column_name)
[{AND/OR} UPDATE(COLUMN_NAME)...]
{ sql_statements }