Mohamed Abdeen

• A trigger is a specialized implementation of a Transact-SQL or CLR batch that automatically runs in response to an event within the database (i.e. run according to change to any object of database)
• Triggers are specialized stored procedure that running run in response to certain event, but it is not having parameters and don’t return value and you can’t run it manually.

• Difference between Triggers & Constraints is Triggers are similar to constraint but need more system overhead so it may lead to performance reduction.

• Triggers application
  • Perform complex business rules validation
  • Cascading data modifications
  • Keep track of changes of every record (Audit)

• Triggers Types
  • Data Manipulation Language Trigger (run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view.)
  • Data Definition Language Trigger (which run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks such as auditing and controlling object access)

• DML Trigger
  • DML triggers are not stand-alone objects, and you can’t directly execute them. A DML trigger is attached to a specific table or view and defined for a particular event. When the event occurs, SQL Server automatically executes the code within the trigger, known as “firing the trigger.” The events that can cause a trigger to fire are INSERTING, UPDATE, and DELETE operations or you can make combinations of previous three types.
  • Triggers can update tables in other databases, and it is possible for triggers to span servers as well so don’t think the scope of trigger is limited to current database.
  • Trigger fire Mode
    • After
      • An AFTER trigger fires after SQL Server completes all actions successfully
      • You can define AFTER triggers only on tables
      • You can create any number of AFTER triggers on a view or table.

• Instead of
  • causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire
  • You can create INSTEAD OF triggers on views and tables
  • You can define only one INSTEAD OF trigger for each INSERT, UPDATE, or DELETE event for a view or table
  • The most common usage is to use INSTEAD OF triggers on views to update multiple base tables through a view
  • If you have an INSTEAD OF trigger that will execute on a data INSERT, then the insertion will not take place.

You can’t use within trigger

• Databases cannot be created, altered, dropped, backed up, or restored.
• Structural changes cannot be made to the table that caused the trigger to fire, such as CREATE/ALTER/DROP INDEX, ALTER/DROP TABLE, and so on.

Nested triggers

For example, imagine you have Table A, which has a trigger on it to fire a modification within Table B, which in turn has a trigger on it that fires a modification within Table C. If a modification is made to Table A, then Table A’s trigger will fire, modifying the data in Table B, which will fire the trigger in Table B, thus modifying data in Table C. This nesting of triggers can go up to 32 triggers deep before you reach the limit set within SQL Server

Note that

SQL Server does not support triggers against system objects such as system tables and dynamic management views. Also, triggers will fire only in response to logged operations. Minimally logged operations such as TRUNCATE and WRITETEXT do not cause a trigger to fire.

Inserted & Deleted tables

Triggers have access to two special tables that are dynamically generated: INSERTED and DELETED. INSERTED and DELETED tables are visible only within a trigger and cannot be accessed by any other construct such as a stored procedure or function. The structure of the INSERTED and DELETED tables exactly matches the column definition of the table on which the trigger was created. Therefore, you can reference columns by using the same name as the table for which the trigger was defined.

The main thought that you must keep in mind when building a trigger is that a trigger fires after each record is flagged to be modified.

Waiting Me @ Trigger…Have a Look Part 2