SQL Server triggers

One, trigger definition and creation:

A trigger (trigger) is a special stored procedure whose execution is not invoked by the program or manually, but is triggered by an event, such as when an operation on a table (insert,delete,update) activates it for execution.

Triggers can be created in Query Analyzer, or by right-click "All Tasks" and "Manage triggers" on the table name, but all are written in T-SQL statements, but in the Query Analyzer you first determine the current operation's database.

Creating a trigger with Create TRIGGER

Basic syntax:

CREATE TRIGGER trigger name on table name for INSERT, UPDATE, or DELETE as   T-SQL statement

The following is an example on Books Online that sends an email notification marym when a record is changed on the titles table.

CREATE TRIGGER Reminder on the titles for INSERT, UPDATE, DELETE as   EXEC master: xp_sendmail ' Marym ',      ' Don ' t forget to print a report for the distributors. '

　　

Second, delete the trigger

1. Delete with Query Analyzer
Use the drop trigger trigger name in Query Analyzer to remove the trigger.
You can also delete multiple triggers at the same time:drop trigger trigger name, trigger name ...
Note: The trigger name is not quoted. Before you delete a trigger, you can see if the trigger exists:

If Exists (select name from sysobjects where name= trigger name and xtype= ' TR ')

　　

2. Delete with Enterprise Manager
In Enterprise Manager, on the table, right-click All Tasks, manage triggers, select the trigger you want to delete, and then tap Delete.

Third, rename the trigger

1. Rename with query analysis

EXEC sp_rename original name, new name

　　

sp_rename is a stored procedure that comes with SQL Server™ to change the name of a user-created object in the current database, such as table name, list, index name, and so on.

2. Rename with Enterprise management
On the table, right-click All Tasks, manage triggers, select the trigger you want to rename, modify the trigger name in the trigger statement, and click OK.

Iv. INSERT, UPDATE, DELETE detailed

　1, INSTEAD of
The SQL statement that executes the trigger statement, but does not execute the trigger, such as when attempting to delete a record, executes the statement specified by the trigger and no longer executes the DELETE statement. Cases:

Create trigger F on tbl instead of Delete as    insert into Logs ...

　　IF UPDATE (column name)
Check if a column has been updated for INSERT or update and cannot be used for delete. Cases:

Create trigger F on TBL for update as    if update (status) or update (title)        sql_statement--update status or title Column

　　2, inserted, deleted
This is a two virtual table, created by itself when an insert, update, or delete operation is made, and inserted saves the table formed by the records affected by insert or update, deleted saves the delete or update The tables that were formed by the records that were previously affected. Cases:

Create trigger Tbl_delete on TBL for delete as    declare @title varchar (a)    select @title =title from deleted   INSERT INTO Logs (logcontent) VALUES (' Delete title: ' + title + ' record ')

　　

Note: If you take a field value from a field type of text, image to a inserted or deleted virtual table, the value you get will be null.

V. View all triggers in the database

1. Run in Query Analyzer:

　

Use database name go select * from sysobjects where xtype= ' TR '

　　

sysobjects holds the object of the database, where the record xtype as TR is the trigger object. We can see the name of the trigger in the Name column.

Vi. sp_helptext viewing trigger content

1. Use Query Analyzer to view

Use database name go EXEC sp_helptext ' trigger name '

　　

The trigger content is displayed in the style of the table.
In addition to triggers, sp_helptext can display rules, default values, unencrypted stored procedures, user-defined functions, view text

2. View with Enterprise Manager

On the table, right-click All Tasks, manage triggers, select the trigger stored procedure that you want to view

Vii. Sp_helptrigger used to view the properties of a trigger

Sp_helptrigger has two parameters: the first parameter is the table name, the second is the trigger type, the char (6) type, can be INSERT, UPDATE, DELETE, and if omitted, displays the properties of all types of triggers in the specified table.

Cases:

Use database name go EXEC sp_helptrigger tbl

　　

Viii. recursive, nested triggers

Recursion is divided into two types, indirect recursion and direct recursion. We explain the following examples, if there are table 1, table 2 names are T1, T2, on T1, T2, respectively, there are trigger G1, G2.

• Indirect recursion: to T1 operation thereby triggering g1,g1 to T2 operation thereby triggering g2,g2 to T1 operation thereby triggering G1 again ...
• Direct recursion: to T1 operation thereby triggering g1,g1 to T1 operation thereby triggering G1 again ...

nested triggers

Similar to the indirect recursion, the indirect recursion must form a ring, and the nested trigger does not have to form a ring, it can t1->t2->t3 ... This continues to trigger, allowing nesting of up to 32 layers.

Set Direct recursion

Direct recursion is disabled by default, and there are two methods to set to allow:

• 1. T-SQL statement:

• exec sp_dboption ' dbName ', ' recursive triggers ', true

  • 2. Use Enterprise Manager: Right-click Properties---options on the database.

  Set indirect recursion, nesting

  By default, indirect recursion, nesting is allowed , and there are two ways to set it to prohibit:

  • 1. T-SQL statement:

    EXEC sp_configure ' nested triggers ', 0--the second parameter is 1 to allow

  • 2. Use Enterprise Manager: Register the server settings, properties, right-click.

  　　

  Nine, Trigger rollback

  We see many registration systems can not change the user name after registration, but this is mostly determined by the application, if you open the database table to make changes, you can also change their user name, in the trigger can be used to implement a rollback in the smart implementation cannot change the user name.

  • Use database name go CREATE TRIGGER TR on table name for update as    if Update (userName)       rollback Tran

    　　

    The key is in the last two sentences, which are interpreted as: If the UserName column is updated, the transaction is rolled back.

    X. Disabling, enabling triggers

    1. Disable the trigger:

    ALTER TABLE name disable TRIGGER trigger name

    2. Enable Trigger:

    ALTER TABLE name enable TRIGGER trigger name

    If there are multiple triggers, separate the name of each trigger with a comma.

    If you change the trigger name to "All", you disable or enable all triggers for that table.

    Xi. how to determine which state the trigger was triggered by

    　　

    If   not   exists (select   *   from   deleted)    
                        /*insert*/  
                If   not   exists (select   *   from   Inserted)    
                        /*delete*/  
                If   exists (select   *   from   Inserted)   and   exists (select   *   from Deleted)  
                        /*update*/   

SQL Server triggers