SQL Server触发器insert update delete示例

作者:袖梨 2022-06-29

·只有inserted表有数据时,当前操作为insert;
·inserted和deleted两张表都有数据时,当前操作为update;
·只有deleted表有数据时,当前操作为delete。

创建触发器用 CREATE TRIGGER

 代码如下 复制代码

CREATE TRIGGER 触发器名称
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS

nserted、deleted
这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录形成的表,deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例:
 

 代码如下 复制代码

 

create trigger tbl_delete
on tbl
for delete
as
    declare @title varchar(200)
    select @title=title from deleted
    insert into Logs(logContent) values('删除了 title 为:' + title + '的记录')

说明:如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时,所取得的值将会是 null。
 
注意:触发器名称是不加引号的。

如下是联机丛书上的一个示例,当在 titles 表上更改记录时,发送邮件通知 MaryM。

 代码如下 复制代码

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.'


# 创建update类型触发器

 代码如下 复制代码

--update更新类型触发器
if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的数据
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的数据
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print '级联修改数据成功!';
        end
    else
        print '无需修改student表!';
go
--查询数据
select * from student order by id;
select * from classes;
update classes set name = '五班' where name = '5班';
     update触发器会在更新数据后,将更新前的数据保存在deleted表中,更新后的数据保存在inserted表中。

 

    # update更新列级触发器

if (object_id('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on classes
    for update
as
    --列级触发器:是否更新了班级创建时间
    if (update(createDate))
    begin
        raisError('系统提示:班级创建时间不能修改!', 16, 11);
        rollback tran;
    end
go
--测试
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = '四班' where id = 7;

     更新列级触发器可以用update是否判断更新列记录;


SQL Server触发器判断当前操作类型(insert/update/delete)示例

 代码如下 复制代码

create trigger updateRowVersion on PPM_JobBill
after insert,update,delete
as
begin
    declare @inserted int, @deleted int
    select @inserted=COUNT(*) from inserted
    select @deleted=COUNT(*) from deleted
    if @inserted>0 and @deleted=0
    begin
        --insert
    end
    else if @inserted>0 and @deleted>0
    begin
        --update
    end
    else if @inserted=0 and @deleted>0
    begin
        --delete
    end
end
go

 

触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。Inserted表的数据是插入或是修改后的数据,而deleted表的数据是更新前的或是删除的数据。

 

对表的操作
Inserted逻辑表
Deleted逻辑表
增加记录(insert)
存放增加的记录
删除记录(delete)
存放被删除的记录
修改记录(update)
存放更新后的记录
存放更新前的记录

    Update数据的时候就是先删除表记录,然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是:触发器本身就是一个事务,所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚,撤销操作。

相关文章

精彩推荐