SQL Server triggers

1. Create a simple trigger
A trigger is a special stored procedure. Similar to an event function, SQL Server allows you to create a trigger for insert, update, and delete, that is, when a record is inserted, updated, or deleted in a table, triggers one or more T-SQL statements.

Triggers can be created in the query analyzer, you can also right-click on the table name-> "all tasks"-> "manage triggers" to create, but are to write T-SQL statements, only in the query analyzer, you must first determine the database for the current operation.

Create trigger

Create trigger name
On Table Name
For insert, update, or delete
As
A T-SQL statement
Note: The trigger name is not enclosed in quotation marks.

The following is an example of online books. When you change the record on the titles table, an email notification is sent to Marym.
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 .'

Ii. delete a trigger
Delete with query Analyzer
Use the drop trigger name in the query analyzer to delete the trigger.
You can also delete multiple triggers: Drop trigger name, trigger name...
Note: The trigger name is not enclosed in quotation marks. Before deleting a trigger, you can check whether the trigger exists:
If exists (Select name from sysobjects where name = trigger name and xtype = 'tr ')

Delete with Enterprise Manager
In Enterprise Manager, right-click a table and choose "all tasks"> "manage triggers". Select the trigger to be deleted and click "delete ".

Iii. rename a trigger
Rename with query Analyzer
Exec sp_rename original name, new name
Sp_rename is a stored procedure that comes with SQL Server. It is used to change the name of the object created by the user in the current database, such as the table name, list, and index name.

Rename with Enterprise Manager
Right-click a table and choose "all tasks"> "manage triggers". Select the trigger to be renamed, modify the trigger name in the trigger statement, and click "OK ".

4. More ....
Instead
Execute the trigger statement, but do not execute the SQL statement that triggers the trigger. For example, if you try to delete a record, the statement specified by the trigger is executed, and the delete statement is no longer executed. Example:
Create trigger F
On TBL
Instead of Delete
As
Insert into logs...

If Update (column name)
Check whether a column is updated for insert or update. It cannot be used for Delete. Example:
Create trigger F
On TBL
For update
As
If Update (Status) or update (title)
SQL _statement -- the status or title column is updated.

Inserted, deleted
This is two virtual tables. Inserted stores the tables that are affected by the insert or update operations. Deleted stores the tables that are affected by the delete or update operations. Example:
Create trigger tbl_delete
On TBL
For Delete
As
Declare @ title varchar (200)
Select @ Title = title from deleted
Insert into logs (logcontent) values ('records with title: '+ title +' deleted ')
Note: If the field value of text or image is obtained from the inserted or deleted virtual table, the obtained value is null.

5. View All triggers in the database
Run the following command in the query Analyzer:

Use Database Name
Go
Select * From sysobjects where xtype = 'tr'
Sysobjects stores database objects. Records with xtype as TR are trigger objects. In the name column, we can see the trigger name.

6. sp_helptext view trigger content
View with query Analyzer

Use Database Name
Go
Exec sp_helptext 'trigger name'
The trigger content is displayed in a table style.
Besides triggers, sp_helptext can also display rules, default values, unencrypted stored procedures, user-defined functions, and view text.

View in Enterprise Manager

Right-click a table and choose "all tasks"> "manage triggers". Select the trigger stored procedure.

7. sp_helptrigger is used to view the trigger attributes.
Sp_helptrigger has two parameters: the first parameter is the table name, the second parameter is the trigger type, char (6) type, can be insert, update, delete, if this parameter is omitted, the attributes of all types of triggers in the specified table are displayed.

Example:

Use Database Name
Go
Exec sp_helptrigger TBL

VIII. recursive and nested triggers
There are two types of recursion: Indirect recursion and direct recursion. For example, if Table 1 and Table 2 are named T1 and T2, And the triggers G1 and G2 are on T1 and T2 respectively.

Indirect recursion: T1 operations trigger G1, G1 operations on T2 to trigger G2, G2 operations on T1 to trigger G1...
Direct recursion: The T1 operation triggers G1, And the G1 operation triggers G1...
Nested triggers

Similar to indirect recursion, indirect recursion must form a ring, while nested triggers do not have to form a ring. It can be T1-> T2-> T3... in this way, a maximum of 32 layers can be nested.

Set direct Recursion

Direct recursion is disabled by default. You can set this parameter to allow two methods:

T-SQL: exec sp_dboption 'dbname', 'cursive trigger', true
EM: Right-click the database and choose "properties"> "option.
Set Indirect recursion and nesting

Indirect recursion and nesting are allowed by default. There are two ways to disable this function:

T-SQL: exec sp_configure 'nested trigger', 0 -- the second parameter is 1 is allowed
EM: Right-click registration and choose Properties> server settings.

9. Trigger rollback
We can see that many registration systems cannot change the user name after registration, but most of these areProgramIf you directly open the database table to change the user name, you can also change the user name. In the trigger, you can use rollback to cleverly achieve that the user name cannot be changed.

Use Database Name
Go
Create trigger tr
On Table Name
For update
As
If Update (username)
Rollback tran

The key lies in the last two sentences. The explanation is: if the username column is updated, the transaction will be rolled back.

10. Disable and enable triggers
Disabled: alter table table name disable trigger name
Enable: alter table table name enable trigger name

If multiple triggers exist, the trigger names are separated by commas.

If you change "trigger name" to "all", all triggers of the table are disabled or enabled.