A trigger is single or multiple SQL statements that fires as an actionwhen event (like INSERT, DELETE,UPDATE, CREATE, ALTER, DROP) occures. You can devide tringgers in to two parts. DDL Triggers and DML Triggers.
DDL (Data Defenition Language) Triggers
DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. 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. They can be used to perform administrative tasks in the database 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 ;
Fig - (1) Example of DDL Trigger
DML (Data Manipulation Language) 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 Trigger
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 Trigger
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. For more information, see Programming CLR Triggers.
DML Trigger Execution
AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.
INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.
If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a DML trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
If an INSTEAD OF trigger defined on a view executes a statement against the view that would usually fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View. For example, if a DML trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.
DML trigger performance overhead is usually low. The time involved in running a DML trigger is spent mostly in referencing other tables, which can be either in memory or on the database device. The deleted and inserted tables are always in memory. The location of other tables referenced by the trigger determines the amount of time the operation requires.
DML Trigger Execution and Partitioned Views
In SQL Server 2005, the way UPDATE and DELETE triggers fire on tables that underlie a partitioned view has changed from the way they fire in SQL Server 2000. In SQL Server 2000, when an UPDATE or DELETE statement is issued against a partitioned view, either local or distributed, any UPDATE or DELETE triggers that are defined on the base tables of the view fire. These include triggers on tables that are not affected by the update or delete operation. In SQL Server 2005, an UPDATE or DELETE trigger fires only if the base table on which the trigger is defined is affected by the update or delete operation. This behavior is the same for both AFTER and INSTEAD OF triggers.
For example, consider a partitioned view PV that consists of fields from tables T1 and T2. Additionally, both T1 and T2 have UPDATE triggers defined on them. In SQL Server 2000, an UPDATE statement issued against PV that only affects rows in T1 causes the UPDATE triggers on both T1 and T2 to fire. In SQL Server 2005, only the UPDATE trigger that is defined on T1 fires.
CREATE TRIGGER TriggerName ON TableName
(FOR / AFTER) INSERT / DELETE / UPDATE
AS
BEGIN
END
Fig - (2) Syntax for DML Trigger
NOTE:
There is no BEFORE trigger in SQL Server.
Understanding DDL Triggers vs. DML Triggers
DDL triggers and DML triggers are used for different purposes.
DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views.
DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.
DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.
Like DML triggers, DDL triggers can run managed code packaged in an assembly that was created in the Microsoft .NET Framework and uploaded in SQL Server. For more information, see Programming CLR Triggers.
Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. Also, a DDL trigger and the statement that fires it are run within the same transaction. This transaction can be rolled back from within the trigger. Serious errors can cause a whole transaction to be automatically rolled back. DDL triggers that are run from a batch and explicitly include the ROLLBACK TRANSACTION statement will cancel the whole batch. For more information, see Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION.
Like DML triggers, DDL triggers can be nested. For more information, see Using Nested Triggers.
When you are designing DDL triggers, consider how they differ from DML triggers in the following ways:
- DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.
- DDL triggers do not create the inserted and deleted tables. The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function. For more information, see Using the EVENTDATA Function.
Important:
This article is copied from different MSDN links. I have just gethered all the information at single place.
Happy Learning !!!
1 comment:
nice artical!!
Post a Comment