SQL Server triggers (10)

SQL Server triggers (10)

I. Most T-SQL statements can be used in statement triggers that cannot be used in triggers, but the following statements cannot be used in triggers.

 

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

 

2. 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 .'

Iii. delete a trigger

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

Iv. rename a trigger

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

5. Complex triggers

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.

6. view the triggers in the current database

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.

7. view trigger content

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.

8. Trigger Extension

1. Storage Process sp_helptrigger

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

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

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

 

9. Disable trigger Activation

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.

10. Trigger 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.

Supplement:

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 two SQL statements in the application 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.