Shulou(Shulou.com)05/31 Report--

This article is about SQL triggers and how they are used. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Create a simple trigger

CREATE TRIGGER TRIGGER NAME ON TABLE NAME FOR INSERT, UPDATE, OR DETECAS T-SQL statements

Note: Trigger names are unquoted.

II. Delete Trigger

drop trigger name

Delete multiple triggers:

drop trigger name, trigger name

III. Rename Trigger

Rename with Query Analyzer

exec sp_rename Original name, new name

sp_rename is a stored procedure that comes with SQL Server and is used to change the name of an object created by a user in the current database, such as a table name, list, index name, etc.

Insert, Update or Delete

INSTEAD OF

Executes the trigger statement but does not execute the SQL statement that triggered the trigger, such as when trying to delete a record, the statement specified by the trigger is executed, and the delete statement is no longer executed. Example:

create trigger fon tblinstead of deleteas insert into Logs...

IF UPDATE(column name)

Checks if a column has been updated, for insert or update, not delete. Example:

create trigger fon tblfor updateas if update(status) or update(title) sql_statement --Updated status or title column

inserted、deleted

These are two virtual tables, inserted holds the table of records affected after insert or update, and deleted holds the table of records affected before delete or update. Example:

create trigger tbl_deleteon tblfor deleteas declare @title varchar(200) select @title=title from deleted insert into Logs(logContent) values ('deleted records with title: ' + title +')

Note: If you take a field value with field type text or image from inserted or deleted virtual table, the obtained value will be null.

5. View all triggers in the database

use database name goselect * from sysobjects where xtype='TR'sysobjects holds database objects, where records with xtype TR are trigger objects. In the name column, we can see the trigger name.

VI. sp_helptext View trigger content

use database name goexec sp_helptext 'trigger name'

sp_helper is used to view the properties of the trigger

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

Example:

use database name goexec sp_helptrigger tbl

VIII. Recursive, nested triggers

There are two types of recursion, indirect recursion and direct recursion. For example, let us explain as follows, if there are Table 1 and Table 2 named T1 and T2 respectively, there are triggers G1 and G2 on T1 and T2 respectively.

Indirect recursion: operate on T1 to trigger G1, G1 operate on T2 to trigger G2, G2 operate on T1 to trigger G1 again...

Direct recursion: operate on T1 to trigger G1, G1 operates on T1 to trigger G1 again...

nested triggers

Similar to indirect recursion, indirect recursion necessarily forms a loop, while nested triggers do not necessarily form a loop, it can be T1->T2->T3... This triggers all the way down, allowing up to 32 nesting levels.

Set direct recursion

Direct recursion is prohibited by default, and there are two ways to set it to allow:

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

EM: Right click on database-> Properties-> Options.

Set indirect recursion, nesting

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

T-SQL: exec sp_configure 'nested triggers', 0 --The second parameter is 1 and is allowed

EM: Register 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 open the database table directly to make changes, you can also change its user name. You can use rollback in the trigger to skillfully realize that you cannot change the user name.

use database name gocreate trigger tron table name for updates if update(userName) rollback tran

The key is in the last two sentences, which explains that if the userName column is updated, the transaction is rolled back.

X. Disable and enable triggers

disabled: alter table table name disable trigger name

Enable: alter table table name enable trigger name

If there are multiple triggers, separate the trigger names with commas.

Replacing Trigger Name with ALL disables or enables all triggers for that table.

XI. Judge insertion, modification and deletion

create TRIGGER [updatetest] ON [dbo]. [TEST] FOR INSERT, UPDATE, DELETE ASbegin DECLARE @IsInsert bit, @IsUpdate bit, @IsDelete bit IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) SET @IsInsert = 1 ELSE SET @IsInsert = 0 IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) SET @IsUpdate = 1 ELSE SET @IsUpdate = 0 IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) SET @IsDelete = 1 ELSE SET @IsDelete = 0 if (@IsUpdate=1) PRINT 'updated ' if (@IsInsert=1) PRINT 'insert ' if (@IsDelete=1) PRINT 'delete 'end Thank you for reading! About "SQL trigger commonly used statements what" this article is shared here, I hope the above content can have some help for everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.