Actions such as trigger creation delete

First, create a simple trigger

A trigger is a special kind of stored procedure, similar to an event function, where SQL Server™ allows you to create triggers for INSERT, UPDATE, DELETE, which triggers one or a series of T-SQL statements when records are inserted, updated, and deleted in a table.

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

CREATE TRIGGER Trigger Name
On table name
For INSERT, UPDATE, or DELETE
As
T-SQL statements
Note: The trigger name is not quoted.

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 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

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 ')

Remove 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

Renaming 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.

Renaming 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.

Four, more ....

INSTEAD of
Executes the trigger statement, but does not execute the SQL statement that triggers the trigger, such as when an attempt is made to delete a record, the statement specified by the trigger executes, and the DELETE statement is no longer executed. 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--Updated status or Title column

Inserted, deleted
This is a table of two virtual tables, inserted that are saved by the records that were affected by the insert or update, and deleted the tables that were formed by the records that were affected before the delete or update was saved. Cases:
Create Trigger Tbl_delete
On TBL
For delete
As
DECLARE @title varchar (200)
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

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

Using 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

View with Enterprise Manager

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

http://hovertree.com/menu/sqlserver/

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:

• T-sql:exec sp_dboption ' dbName ', ' recursive triggers ', true
• EM: Options on the database, right-click Properties.

Set indirect recursion, nesting

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

• T-sql:exec sp_configure ' nested triggers ', 0--the second parameter is 1 to allow
• EM: Register 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

Disabled: ALTER TABLE name disable TRIGGER trigger name
Enable: 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.

Recommendation: http://www.cnblogs.com/roucheng/p/3541165.html

Actions such as trigger creation delete