SQL Server trigger details, SQL Server trigger

SQL Server trigger details, SQL Server trigger

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events, its execution is not called by a program, nor is it manually started, but triggered by an event. For example, when a table is operated (insert, delete, update), it is activated for execution.

Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary. The SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

Triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements. For example, you can control whether to insert a new order based on the current account status of the customer.

Triggers can also be used to force reference integrity so that the relationships defined between these tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to force reference integrity is to define the primary key and foreign key constraints in the relevant table. If you use a database relationship diagram, you can create a relationship between tables to automatically create a foreign key constraint.

The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement.

Query all triggers in the database:

select * from sysobjects where xtype='TR'

1. Syntax

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 change 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 'rollback tran; endelseprint' cannot be deleted successfully'

Graphic introduction trigger

Database running environment SqlServer2005

A trigger is a special stored procedure. Its execution is not called by a program or started manually, but triggered by an event. When a table is operated (insert, delete, update) is activated for execution. triggers are often used to enhance data integrity constraints and business rules. In fact, simply put, a trigger is a switch, which is responsible for the light on and off. If you change it, it will light up.

Trigger category

1 DML (Data Manipulation Language) trigger: indicates that the trigger is enabled when a DML event occurs in the database. DML events are the insert, update, and delete statements that modify data in tables or views.

2 DDL (Data Definition Language) trigger: It is used when a DDL event occurs on the server or database. DDL events refer to the create, alter, and drop statements in tables or indexes.

3. logon trigger: This trigger is triggered when a user logs on to the SQL SERVER instance to establish a session.

DML triggers

1 in SQL SERVER 2008, DML triggers use two logical tables DELETED and INSERTED. These two tables are created in the memory of the database server. We only have read-only permissions. The structure of the DELETED and INSERED tables is the same as that of the data table where the trigger is located. After the triggers are executed, they will be automatically deleted: The INSERED table is used to store the updated records after the insert, update, and delete statements are executed. For example, if you insert a data record, the record is INSERTED to the INSERTED Table: The DELETED table is used to store the database in the trigger table before you operate the insert, update, and delete statements.

2. The trigger can implement cascade changes through the relevant tables in the database, which can be more complex than the constraints defined by the CHECK constraint. Unlike CHECK constraints, triggers can reference columns in other tables. For example, triggers can use SELECT statements in another table to compare inserted or updated data and perform other operations. The trigger can also take countermeasures based on the table status before and after data modification. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different countermeasures to respond to the same modification statement.

3 At the same time, although the trigger has powerful functions and can easily and reliably implement many complex functions, why should we use it with caution? Too many triggers may make it difficult to maintain databases and applications. At the same time, excessive dependencies on triggers will inevitably affect the database structure and increase the maintenance of complex programs.

Trigger steps

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

Create trigger mytrigger on AddTable
For update

2. the SQL statement is followed. It mainly requires the trigger to trigger an operation after an update occurs. This means that if an update occurs, the trigger will trigger the output: the table was updated! --- By pig is helpless.

3. Next we will perform a change operation on the data in the AddTable table:

4. After execution, we will find that the trigger is triggered and the set text is output:

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

6. To view the content of a trigger, run exec sp_helptext [Trigger name].

7 if I want to query the number of triggers in the current database to facilitate database maintenance, just run:

select * from sysobjects where xtype='TR'

8. To disable or enable a trigger, run the following command:

Disable trigger [trigger name] on database -- disable trigger

Enable trigger [trigger name] on database -- enable trigger

9. The trigger functions are large, but once triggered, it will be difficult to recover. We need to protect the data. Here we need to use rollback data rollback ~

10. The ninth step is to query the AddTable table. If TableName = newTable exists in the table, the data will be rolled back and the trigger will stop. Then we will perform a test to modify the AddTable table, after an update trigger is triggered, the trigger is aborted due to data protection:

Notes

You must have certain permissions to disable and enable triggers. If you do not have sufficient permissions, you cannot perform operations.

Note the error prompt after running, which is helpful for correcting the error.