Triggers in SQL

A trigger is a special type of stored procedure that is automatically invoked when the data in a specified table or view is modified. The INSERT, UPDATE and DELETE statements can invoke a trigger. A trigger has the capability to query other tables and includes complex SQL statements.

A trigger can be used to reference or update data in another database or even on another server. When a trigger is fired, it becomes a part of the transaction from which the original data was modified. If a server error occurs, the entire transaction is rolled back automatically.

Differentiate between DML and DDL triggers

• DDL trigger is a new feature added to SQL Server 2005, whereas DML triggers were user even with SQL Server 7.0 and 2000.
• DML triggers execute many SQL statements or procedure on execution of a DML statement, like INSERT, UPDATE or DELETE.
• SQL Server 2005 permits a user to create a trigger that can perform an action in case a DDL statement is executed. These triggers are used when the user wants to performs an action when a schema change occurs.
• A DDL trigger is a type of stored procedure that is executed automatically when an event occurs in the database server.
• DDL triggers are executed in response to the various data definition language (DDL) events, such as CREATE, ALTER, and DROP statements.

Limitations of Triggers

• The CREATE TRIGGER statement can apply to only one table. Moreover, it must be the first statement in the batch.
• Permission to create triggers on a table or view defaults to the owner of the table or view.
• Triggers can only be created in the current database, although they can reference objects outside the current database.
• Triggers cannot be created on a temporary table or system table. Temporary tables can be created and referenced by a trigger. Instead of referencing system tables, you should reference the Information Schema Views.
• If a table contains a foreign key with an UPDATE or DELETE cascading action defined, then INSTEAD OF UPDATE or INSTEAD OF DELETE triggers should not be defined on the table.
• The TRUNCATE TABLE statement will not fire a DELETE trigger because this action is not logged.
• The WRITETEXT statement, whether logged or unlogged, will not activate a trigger.

Q. What are the two main methods for enforcing business rules and data integrity at the server?

A. The two main methods for enforcing business rules and data integrity at the server are constraints and triggers. The main benefit of triggers over constraints is the capability to perform complex processing logic using SQL.

Related Post: Triggers in Detail