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

In this issue, the editor will bring you about the role of triggers in SqlServer. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Query all triggers in the database:

Select * from sysobjects where xtype='TR'

1. Grammar

Create trigger [shema_name. ] Trg_nameon {table | view} [with encryption] {for | after | instead of} {insert, update, delete} assql_statement

Insert trigger instance

Create trigger teston alfor insertasdeclare @ id int,@uid int,@lid int,@result charselect @ id=id,@uid=uid,@lid=lid,@result=result from insertedif (@ lid=4) begin update al set uid=99 where id=@id print 'lid=4 automatically changes the user id to 99'end

Update trigger instance

Create trigger test_updateon al for updateas declare @ oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int select @ oldid=id,@olduid=uid,@oldlid=lid from deleted; select @ newid=id,@newuid=uid,@newlid=lid from inserted if (@ newlid > @ oldlid) begin print 'newlid > oldid' rollback tran; end else print' modified successfully'

Delete trigger instance

Create trigger test_deleteon alfor deleteasdeclare @ did int,@duid int,@dlid intselect @ did=id,@duid=uid,@dlid=lid from deletedif (exists (select * from list where @ dlid=id)) beginprint 'cannot delete' rollback tran;endelseprint 'deleted successfully'

Picture and text introduction trigger

Database running environment SqlServer2005

Trigger (trigger) is a special stored procedure, its execution is not called by the program, nor started manually, but triggered by events. When operating on a table (insert,delete, update), trigger will activate its execution. Triggers are often used to strengthen data integrity constraints and business rules. In fact, to put it simply, the trigger is a switch, responsible for the light on or off, you move, it will light up, that's what it means.

Classification of triggers

1 DML (data manipulation language Data Manipulation Language) trigger: means that the trigger will be enabled when a DML event occurs in the database. DML events are insert, update, and delete statements that modify data in a table or view.

2 DDL (data definition language Data Definition Language) trigger: it is enabled when a DDL event occurs in the server or database. DDL events refer to create, alter, and drop statements in a table or index.

3 login trigger: it is triggered when the user logs in to the SQL SERVER instance to establish a session.

Introduction to DML Flip-flop

1 in SQL SERVER 2008, the implementation of DML triggers uses two logical tables DELETED and INSERTED. These two tables are built in the memory of the database server, and we only have read-only permissions. The structure of the DELETED and INSERED tables is the same as the structure of the data table where the trigger is located. When the triggers are executed, they are automatically deleted: the INSERED table is used to store the records you updated after the insert, update, and delete statements. For example, if you insert a piece of data, the record will be inserted into the INSERTED table: the DELETED table is used to hold the database in the trigger table that you create before you manipulate the insert, update, and delete statements.

2 triggers can cascade changes through related tables in the database and enforce more complex constraints than those defined with CHECK constraints. Unlike CHECK constraints, triggers can reference columns in other tables, for example, triggers can use SELECT in another table to compare inserted or updated data, and perform other operations. The trigger can also take countermeasures according to the table state before and after the data modification. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different countermeasures to be taken in response to the same modification statement.

At the same time, although triggers are powerful and easily and reliably implement many complex functions, why should they be used cautiously? Too many triggers will cause difficulties in the maintenance of databases and applications. At the same time, over-reliance on triggers will inevitably affect the structure of the database and increase the maintenance of complex programs.

Detailed explanation of trigger steps

1 first, let's try to create a trigger. The requirement is to create a Update trigger on the AddTable table with the following statement:

Create trigger mytrigger on AddTablefor update

2 then comes the part of the sql statement, which is mainly what action the trigger is required to trigger if update occurs. What this means is that if update appears, the trigger will trigger the output: the table was updated Murray by piglets are also helpless.

3 next let's perform a change operation on the data in the AddTable table:

4 after execution, we will find that the trigger is triggered to output the text we set:

5 after the trigger is created, it will officially start to work. If we need to change the trigger, we just need to change the initial create creation to alter, and then modify the logic:

6 if we want to view the contents of a trigger, run directly: exec sp_helptext [trigger name]

7 if I want to query how many triggers are in the current database to facilitate my database maintenance, just run:

Select * from sysobjects where xtype='TR'

8 if we need to turn off or turn on the trigger, we just need to run:

Disable trigger [trigger name] on database-disables triggers

Enable trigger [trigger name] on database-- opens the trigger

9 although the function of the trigger is large, but once triggered, it will be troublesome to recover. Then we need to protect the data. Here we need to use rollback data rollback.

Step 9 means to query the AddTable table. If there is TableName=newTable in it, the data will be rolled back and the trigger will be aborted. Then we will test again and make changes to the AddTable table. We find that after triggering the update trigger, the trigger is aborted because of data protection:

Matters needing attention

Certain permissions are required to disable and turn on triggers, which cannot be operated if the permissions are not enough.

Pay attention to the error prompt after running, which is very helpful to correct the error.

This is what the role of triggers in SqlServer is shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.