SQL trigger topic document

The previous document on triggers is provided. Triggers are important to SQL Server.

SQL Server: Basic trigger documentation

SQLServer Database Development trigger application

A trigger is a special stored procedure, similar to the event functions in other programming languages,SQLServer allows the creation of triggers for insert, update, and delete. When a record is inserted, updated, or deleted in a table (view), one or more T-SQLStatement.

The purpose of this topic is not to introduce the trigger in detail, but to systematically introduce you to the common knowledge and related applications of the trigger and discuss the application experience with you, if you want to learn more about the uncommon triggers, seeSQLServer books online (InstallationSQLServer ).

Creating a common trigger in a view may result in an "invalid object" error. In fact, we cannot create a for trigger in a view, but should create an instead of trigger. InSQLIn server books online, it is not said that a trigger cannot be created in a view, and the syntax explanation shows that the trigger can be a view after the create trigger on. However, this does not seem to be the case. Many experts also say that triggers cannot be created on The View. I have also made a special test. Indeed, neither a common view nor an index view can create a trigger on it. however, it is understandable that a trigger is rejected when a temporary table or system table is created.

For create trigger statement

The for keyword can be followed by one or more insert, update, and delete keywords. That is to say, triggers will not be triggered in other cases, including select, truncate, writetext, and updatetext.

The truncate table and the delete function without where are the same. All data in the table is deleted. However, the truncate table is faster and consumes fewer logs, this is because the truncate table directly releases the data page and only records the release of the data page in the transaction log. The delete operation records the deletion of each record in the transaction log in one row.

Can we use truncate table instead of delete without where? It is not possible in the following situations:
1. The truncate table cannot be used when the ID is to be retained, because the truncate table will reset the ID.
2. The truncate table cannot be used when a trigger is required. It does not trigger the trigger.
3. The truncate table cannot be used for tables referenced by the foreign key constraint (that is, the table where the primary key is located, not the table where the foreign key is located.
4. The truncate table cannot be used for tables that participate in the index view. Note that the index view is not a common view.

When an error occurs in the internal statement of the trigger,The previous data change operation will be invalid.. For example, if a trigger is triggered when data is inserted in a table and a running error occurs in the trigger, an error value is returned and data insertion is rejected.

[Nextpage]

An interesting trigger applicationTrigger rollback

We can see that many registration systems cannot change the user name after registration, but most of them are determined by the application. If you directly open the database table to change the user name, you can also change the user name, by using rollback in a trigger, 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.

Statements that cannot be used in triggers

Most T-SQLStatement, but the following statements cannot be used in the trigger.
Create statement, such as create database, create table, and create index.
Alter statements, such as alter database, alter table, and alter index.
Drop statements, such as drop database, drop table, and drop index.
Disk statement, such as disk init and disk resize.
Load statement, such as load database and load log.
Restore statements, such as Restore database and restore log.
Reconfigure
Note: It is said that the truncate TABLE statement cannot be used.

[Nextpage]

Recursive and nested triggers

Recursive trigger

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 -- if the second parameter is 1
EM: Right-click registration and choose Properties> server settings.

There are two types of recursive triggers: indirect 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 are 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. -------------------------------------------------------------------------------- To set direct recursion, direct recursion is prohibited by default. To allow direct recursion, set T-SQL: Exec sp_dboption 'dbname', 'cursive trigger', true EM: Right-click the database and choose Properties> option. Indirect recursion and nesting are allowed by default. To disable indirect recursion, T-SQL: Exec sp_configure 'nested trigger', 0 -- if the second parameter is 1, allow EM: Register, right click, choose Properties> server settings.

[Nextpage]

Multiple triggers

Trigger names are different, and trigger events are the same (insert, update, delete). Multiple triggers are called.

The execution sequence between multiple triggers is not determined.

I personally think we should avoid using multiple triggers because it is not conducive to maintenance.

View the content of a trigger

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 expected trigger.

View the triggers in the current 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.

[Nextpage]

Delete 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 ".

Rename a trigger
Rename with query Analyzer
Exec sp_rename original name, new name
Sp_rename isSQLServer comes with a stored procedure that is used to change the names of objects created by users in the current database, such as table names, lists, and index names.

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

View trigger attributes

The stored procedure sp_helptrigger is used to view the attributes of a trigger.

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

[Nextpage]

More triggers

Instead
Execute the trigger statement, but do not execute the triggerSQLStatement. For example, if you try to delete a record, the statement specified by the trigger is executed and the delete statement is not 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.

[Nextpage]

Use trigger with caution
The trigger has powerful functions and can easily and reliably implement many complex functions. Why should we use it with caution. Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance.

In database operations, we can perform data operations through relationships, triggers, stored procedures, applications, etc. For example, we hope to delete records related to table T2 during table T1 deletion, in this case, you can establish a cascading deletion relationship, or create a trigger for table T1 to delete records related to table T2 at the same time, or customize the stored procedure to delete records of table T1 and table T2, you can also use twoSQLStatement to delete ...... Which one is better? We should say that it is best to establish a link to achieve cascading deletion unless there is a higher demand.

A trigger can also be used to ensure data integrity, but rules, constraints, and default values can also ensure data integrity. Which one is better? Generally, the simple integrity requirement is that triggers should not be used. The two are also different in the operating mechanism, such as rules, constraints, and default values, which are used for data verification before data changes, the trigger is verified after the data changes (if the transaction is rolled back, the table will not change ).

In short, if we rely too much on the trigger, it will cause a situation where the program is everywhere, because the trigger itself requires another program to give it a trigger condition, that is to say, there are programs in at least two places. At the same time, we discard constraints and default values and select triggers, which will inevitably affect the database structure.

 

 

 

Http://www.86oo.com/html/176/5275.html