SQL Server: Detailed explanation of triggers

1. Overview

A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted, updated, or deleted into the table. So triggers can be used to implement complex integrity constraints on tables.

2. Classification of triggers

SQL Server2000 provides two types of triggers: "Instead of" and "After" triggers.

Each modification action (Insert, Update, and Delete) of a table or view can have an "Instead of" trigger, and each modification action of a table can have multiple "After" triggers.

2.1 “Instead of” trigger

The “Instead of” trigger is executed before the actual “insert” is executed. In addition to tables, "Instead of" triggers can also be used on views to extend the update operations that the view can support.

The "Instead of" trigger will replace the SQL statement to be executed, which means that the SQL statement to be executed will not be "actually executed"

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    select 学号, 姓名 from deleted
end
 
delete from 学生 where 学号 = 4

In the above example, the "trigger student_Delete" trigger is defined. The processor prints out the students to be deleted from the "delete" table. After executing the "delete" operation, you will find that the student with "student number = 4" has not been deleted. The reason is that "trigger student Delete" replaces the "trigger student Delete" that is to be executed. delete from student where student number = 4" statement, but the student is not actually deleted in "trigger student_Delete".

2.2 “After” trigger

The “After” trigger is triggered after the Insert, Update or Deleted statement is executed. "After" triggers can only be used on tables.

The "After" trigger is mainly used to modify other tables after the table is modified (after insert, update or delete operations)

3. Inserted and Deleted tables

SQL Server creates two triggers for each Special tables: Inserted table and Deleted table.

These two tables are maintained by the system. They exist in memory rather than in the database and can be understood as a virtual table.

The structure of these two tables is always the same as the structure of the table acted upon by the trigger.

After the trigger execution is completed, the two tables related to the trigger are also deleted.

The Deleted table stores all rows that are to be deleted from the table due to the execution of Delete or Update statements.

The Inserted table stores all rows to be inserted into the table due to the execution of Insert or Update statements.

4. Trigger execution process

If an Insert, update or delete statement violates the constraint, then this SQL statement will not be executed successfully, so the "After" trigger will not be activated.

An “Instead of” trigger can be executed instead of the action that fired it. It is executed when the Inserted table and Deleted table have just been created and no other operations have occurred. Because the "Instead of" trigger executes before the constraint, it can do some preprocessing of the constraint.

5. Create trigger

create trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

6. Modify trigger:

alter trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

7. Delete trigger:

drop trigger trigger_name

8. View database There is already a trigger in:

8.1 View all triggers in the database

select * from sysobjects where xtype='TR'

8.2 View a single trigger

exec sp_helptext '触发器名'

9. "Instead of" related examples:

Two tables: student (student number int, name varchar), borrowing records (student number int, book number int)

Function implementation: when deleting the student table, if the student still has a borrowing record (unreturned), it cannot be deleted

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end

10. "After" trigger

10.1 Create a trigger in the "Order" table. When inserting an order record into the "Order" table, check whether the "Status" of the product status in the "Product" table is 1 (organizing). Then the order cannot be added to the "Orders" table.

create trigger trigger_订单_insert
on 订单
after insert
as
    if (select 状态 from 商品, inserted where 商品.pid = inserted.pid)=1
    begin
        print 'the goods is being processed'
        print 'the order cannot be committed'
        rollback transaction --回滚，避免加入
    end

In this example, "pid" is the product code

The if judgment of this example is strictly speaking inaccurate, because if a record is inserted into the "Order" table each time, there is no problem with the judgment; if it is once If multiple records are inserted, the "select status" returns multiple rows.

10.2 Create an insertion trigger in the "Order" table, and when adding an order, reduce the inventory in the corresponding product record in the "Product" table.

create trigger trigger_订单_insert2
on 订单
after insert
as
    update 商品 set 数量 = 数量 - inserted.数量
    from 商品, inserted
    where 商品.pid = inserted.pid

10.3 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。

create trigger goodsdelete trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted)

10.4 在“订单”表建立一个更新触发器，监视“订单”表的“订单日期”列，使其不能被“update”.

create trigger trigger_订单_update
on 订单
after update
as
    if update(订单日期)
    begin
        raiserror('订单日期不能手动修改',10,1)
        rollback transaction
    end

10.5 在“订单”表建立一个插入触发器，保证向“订单”表插入的货品必须要在“商品”表中一定存在。

create trigger trigger_订单_insert3
on 订单
after insert
as
    if (select count(*) from 商品, inserted where 商品.pid = inserted.pid)=0
    begin
        print '商品不存在'
        rollback transaction
    end

10.6 “订单”表建立一个插入触发器，保证向“订单”表插入的货品信息要在“订单日志”表中添加

alter trigger trigger_订单_insert
on 订单
for insert
as
    insert into 订单日志 select inserted.Id, inserted.pid,inserted.数量 from inserted