SQL Server producer (3)

      A trigger is a special stored procedure that is closely linked to a table. When you modify the data in a table, the trigger is automatically executed. The trigger can use the transact-SQL statement to perform complex logic processing. It is created based on a table, however, you can operate on multiple tables, so they are often used for complex business rules. The syntax for creating a trigger is as follows:

Create trigger trigger_name
On {table | view}
[With encryption]
{
{For | after | instead of} {[insert] [,] [update]}
 [With append]
 [Not for replication]
As
[{If Update (column)
[{And | or} Update (column)]
[... N]
| If (columns_updated () {bitwise_operator} updated_bitmask)
{Comparison_operator} column_bitmask [... n]
}]
SQL _statement [... n]
}
}

The parameters in the create trigger syntax are described in the table.

Table  Create a parameter list in the trigger syntax

ParametersDescription
 
Trigger_name
 The name of the trigger. The trigger name must comply with the identifier rules and be unique in the database. You can choose whether to specify the trigger owner name.
 
Table | View
 The table or view on which the trigger is executed, sometimes called the trigger table or trigger view. You can choose whether to specify the table or view owner name.
 
With Encryption
 Encrypt entries in the syscomments table that contain the create trigger statement text. Use with encryption to prevent publishing of triggers as part of SQL Server replication
 
After
 The specified trigger is triggered only when all specified operations in the SQL statement are successfully executed. This trigger can be executed only after the reference cascade operations and constraints check are completed successfully.
 
Instead
 Specify the trigger to be executed, instead of the SQL statement to be triggered, to replace the trigger statement operation.

In a table or view, each insert, update, or delete statement can define up to one instead trigger. However, you can define a view on each view with an instead of trigger.

The instead of trigger cannot be defined in the updatable view with check option. If an instead of trigger is added to the updatable view with the check option specified, SQL Server generates an error. You must use alter view to delete this option before defining the instead of trigger.
 
{[Delete] [,] [insert] [,] [update]}
 Specifies which data modification statements are executed on the table or view to activate the trigger keywords. You must specify at least one option. These keywords can be combined in any order in the trigger definition. If more than one option is specified, separate the options with commas (,).

For an instead of trigger, the delete option cannot be used on a table with the on Delete cascade operation reference relationship. Similarly, you cannot use the update option on a table that has an on update cascade operation reference relationship.
 
With append
 Specify other triggers of the existing type. This optional clause is required only when the compatibility level is 65 or lower. If the compatibility level is 70 or higher, you do not need to use the with APPEND clause to add other triggers of the existing type.
 
Not for replication
 Indicates that the trigger should not be executed when the replication process changes the table involved by the trigger
 
As
 Action to be performed by the trigger
 
SQL _statement
 Trigger conditions and operations. Specify other criteria for the trigger condition to determine whether the delete, insert, or update statement causes the trigger to be executed.

When the delete, insert, or update operation is attempted, the trigger operation specified in the transact-SQL statement takes effect.
 

When a trigger is triggered:

The After trigger is triggered after an operation (insert, update, or delete) and after any constraints are processed. You can request an After trigger by specifying the after or for keyword. Because the for keyword has the same effect as the after keyword, triggers with the for keyword are also classified as the after trigger.
The instead trigger instead of the trigger action is triggered before processing the constraint. For each trigger operation (Update, delete, and insert), each table or view can have only one instead trigger. A table can have multiple after triggers for each trigger operation.
  Example Trigger creation and Application

In this example, the student information in the deleted class is automatically deleted when the class table is deleted.

The program code is as follows:

If exists (Select name
     From Sysobjects
     WhereName = 'trig _ del'
     And     Type = 'tr ')
   Drop trigger trig_del
Go
Create trigger trig_del
On t_tclass
For Delete
As
Begin
  Declare @ ID varchar (10)
  Select @ ID = t_trigclss from deleted
  Delete from t_tstu where t_trigclass = @ ID
End