Trigger is Sql Server 2008

Trigger is Sql Server 2008

Trigger Definition

A trigger is a special type of stored procedure that automatically runs when a language event executes. SQL Server includes two general types of triggers:

data manipulation language (DML) and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements modify data

in a specified table or view. DDL triggers fire stored procedures in response to a variety of DDL statements, which are primarily statements that begin with

CREATE, ALTER, and DROP. DDL triggers can be used for administrative tasks, such as auditing and regulating database operations.

Types of DML Triggers

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.

Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.

AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,

where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger.

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly

created in the .NET Framework and uploaded in SQL Server.

DDL Triggers

-DDL triggers execute in response to a variety of data definition language (DDL) events.

These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

- Unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of

Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain

system stored procedures that perform DDL-like operations can also fire DDL triggers

DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

Use DDL triggers when you want to do the following:

-You want to prevent certain changes to your database schema.

-You want something to occur in the database in response to a change in your database schema.

-You want to record changes or events in the database schema.

DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK ;

DML Triggers

DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view. A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML triggers are useful in these ways:

-They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential

integrity constraints.

-They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with

CHECK constraints.

-Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted

or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

-They can evaluate the state of a table before and after a data modification and take actions based on that difference.

Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to

the same modification statement.

Types of DML Triggers

You can program the following types of DML Triggers:

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.

Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.

AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,

where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure,

a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.