SQL Server series: triggers

Some common uses of triggers:

◊ Enforce Referential integrity

◊ Common audit trails (Audit trails): This means that the written records not only track most of the current data, but also include historical data that is actually modified for each record.

◊ Create features similar to check constraints: Unlike check constraints, this functionality can be used across tables, across databases, and even across servers.

◊ replaces the user's action statement with its own statement: An insert that is often used to start a complex view.

1. The concept of triggers

A trigger is a special type of stored procedure that responds to a specific event. There are two types of triggers: data definition language triggers and data manipulation language triggers.

The data definition LANGUAGE,DDL trigger responds when the user modifies the database structure in some way (Cerate, ALTER, DROP). DDL triggers are used only when the database structure changes or history is critically audited.

Data manipulation LANGUAGE,DML triggers are snippets of code attached to a particular table or view. Unlike the stored procedure that needs to display the call, the code in the trigger runs automatically whenever an event with an attached trigger occurs in the table. It is not really possible to invoke the trigger explicitly, the only way to do so is to perform the desired action in the specified table.

Type of Trigger:

◊insert Trigger

◊delete Trigger

◊update Trigger

◊ any type of mixing above

2. Create a Trigger

To create a trigger syntax:

CREATE TRIGGER [schema_name.]trigger_name on{Table | View } [With <dml_trigger_option> [,... n] ]{  for |After|INSTEAD of } { [INSERT] [ , ] [UPDATE] [ , ] [DELETE] } [With APPEND][Not for REPLICATION]  as{sql_statement[ ; ] [,... N] |EXTERNAL NAME<Method specifier[ ; ] > }<Dml_trigger_option>::=    [Encryption]    [EXECUTE as Clause]<Method_specifier>::=Assembly_name.class_name.method_name

SQL Server series: triggers