Shulou(Shulou.com)06/01 Report--

First, create a simple trigger

Triggers are special stored procedures similar to event functions. SQL Server allows you to create triggers for INSERT, UPDATE, and DELETE, that is, when a record is inserted, updated, or deleted in a table, one or a series of T-SQL statements are triggered.

Triggers can be created in query Analyzer or by right-clicking on the table name-> "all tasks"-> "manage triggers". However, you need to write T-SQL statements, but you need to determine the database for the current operation in the query analyzer.

Create CREATE TRIGGER for triggers

CREATE TRIGGER trigger name

ON table name

FOR INSERT, UPDATE or DELETE

AS

T-SQL statement

Note: trigger names are unquoted.

The following is an example from Books online that sends an email to notify 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.'

2. Delete the trigger

Delete with query Analyzer

Use the drop trigger trigger name in the query analyzer to delete the trigger.

You can also delete multiple triggers at the same time: drop trigger trigger name, trigger name.

Note: trigger names are unquoted. You can check to see if the trigger exists before deleting the trigger:

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

Delete with Enterprise Manager

In Enterprise Manager, right-click on the table-> all tasks-> manage triggers, select the trigger you want to delete, and then click Delete.

III. Rename the trigger

Rename with query Analyzer

Exec sp_rename original name, new name

Sp_rename is a stored procedure that comes with SQL Server to change the names of user-created objects in the current database, such as table names, lists, index names, and so on.

Rename with Enterprise Manager

Right-click on the table-> all tasks-> manage triggers, select the trigger you want to rename, modify the trigger name in the trigger statement, and click OK.

IV. More....

INSTEAD OF

Execute the trigger statement, but not the SQL statement that triggered the trigger. For example, when 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 to see if a column has been updated for insert or update, not for delete. Example:

Create trigger f

On tbl

For update

As

If update (status) or update (title)

Sql_statement-updated the status or title column

Inserted 、 deleted

These are two virtual tables, inserted saves tables formed by records affected after insert or update, and deleted saves tables formed by records previously affected by delete or update. Example:

Create trigger tbl_delete

On tbl

For delete

As

Declare @ title varchar

Select @ title=title from deleted

Insert into Logs (logContent) values ('deleted the record whose title is:' + title +')

Note: if you take the field value of field type text or p_w_picpath from the inserted or deleted virtual table, the value will be null.

5. View all triggers in the database

Run in query Analyzer:

Use database name

Go

Select * from sysobjects where xtype='TR'

Sysobjects holds the objects of the database, and the record in which xtype is TR is the trigger object. In the name column, we can see the trigger name.

6. Sp_helptext to view the trigger content

View with query Analyzer

Use database name

Go

Exec sp_helptext 'trigger name'

The contents of the trigger are displayed in the style of the table.

In addition to triggers, sp_helptext can also display rules, default values, unencrypted stored procedures, user-defined functions, and view text

View with Enterprise Manager

Right-click on the table-> all tasks-> manage triggers, and select the trigger stored procedure you want to view

7. Sp_helptrigger is used to view the properties of triggers.

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

Example:

Use database name

Go

Exec sp_helptrigger tbl

8. Recursive, nested triggers

There are two kinds of recursion, indirect recursion and direct recursion. We give an example to explain as follows: if the names of tables 1 and 2 are T1 and T2 respectively, and there are triggers G1 and G2 on T1 and T2, respectively.

Indirect recursion: the T1 operation triggers the G1 Magi G1-to-T2 operation, which triggers the G2 Magi G2-to-T1 operation and triggers G1 again.

Direct recursion: the operation on T1 triggers the G1 Magi G1-to-T1 operation and triggers G1 again.

Nested trigger

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. This keeps triggering, allowing up to 32 layers to be nested.

Set up direct recursion

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

T-SQL:exec sp_dboption 'dbName',' recursive triggers', true

EM: right-click on the database-> Properties-> option.

Set indirect recursion, nesting

Indirect recursion and nesting are allowed by default, and there are two ways to set it to disable:

T-SQL:exec sp_configure 'nested triggers', 0-- allow if the second parameter is 1

EM: register the right click-> Properties-> Server Settings.

IX. Trigger rollback

We see that many registration systems cannot change the user name after registration, but this is mostly determined by the application. If you directly open the database table to make changes, you can also change the user name. Rollback in the trigger can be skillfully realized 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, which are interpreted as: if the userName column is updated, the transaction is rolled back.

Disable and enable triggers

Disable: alter table table name disable trigger trigger name

Enable: alter table table name enable trigger trigger name

If there are multiple triggers, the trigger names are separated by commas in English.

If you replace the trigger name with ALL, all triggers for the table are disabled or enabled.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.