15、SQL Server 触发器

SQL Server 触发器

触发器是一种特殊的存储过程，只有当试图用数据操作语言DML来修改数据时才会触发，DML包含对视图和表的增、删、改。

触发器分为DML触发器和DDL触发器，其中DML触发器会对数据表，视图进行insert、update、delete的时候触发。DDL则是create、alter、drop等时触发。

DML触发器又分为after（之后触发）和insert of（之前触发）触发器。

如果在创建触发器的时候不指定类型，则默认是after触发器。

触发器有两个虚拟的临时表：插入表（inserted）和删除表（deleted），由系统在内存中创建，不会保存到数据库。并且两张表都是只读的。当触发器完成工作后，这两张表会自动删除。

增加：inserted表 记录

删除：deleted表  记录

修改：inserted表记录修改前 deleted表记录修改后

修改数据时现删掉该记录，然后再增加一天新的记录。这样inserted和updated就都存在记录了。

触发器本身就是一个事务，可以利用事务的回滚，撤销操作。事务就是要么都执行，要么一条都不执行。

创建触发器

语法：

create trigger [shema_name . ] trg_name
on { table | view }
[ with encryption ]
{ for | after | instead of }
{ insert , update , delete }
as
sql_statement

创建insert触发器

if(object_id('tgr_test_insert','tr') is not null)
    drop trigger tgr_test_insert --删除触发器
go
create trigger tgr_test_insert
on student --创建在student表
    for insert --insert触发
as
declare @id int,@name varchar(20),@sex char(2),@age int
--查询inserted表中已经插入的信息
select @id = id,@name = name,@sex = sex, @age = age from inserted
--用查到的信息加入到会员表中
if(@age >30) --如果年龄不符合 则修改
begin
    update student set age = 30 where id = @id
    print '年龄太大，已自动修改为30'
end
insert into [user] (userName,passWord,RoleId) values (@name,@sex + cast(@age as varchar),3)
print '添加学生成功！';
print '添加会员成功！'

对student表创建触发器后对该表执行insert后

insert into student (name,sex,age,mail) values ('海盗船长','男',88,'[e-mail]')

查看运行结果

创建update触发器

if(object_id('tgr_student_update','tr') is not null)
    drop trigger tgr_student_update
go
create trigger tgr_student_update
on student
    for update
as
    declare @oldName varchar(10),@newName varchar(10)
    select @oldName = name from deleted; --查找更新前的数据
    select @newName = name from inserted;--查找更新后的数据
    if(@oldName = @newName)
    begin
        print '数据相同'
        rollback tran;   --回滚 不执行修改操作
    end
    else
        print '修改成功'

update student set name='海盗船长' 

事务在触发器中结束。批处理已中止。

update student set name='粉红娘娘'

修改成功

创建delete触发器

if(object_id('tgr_student_delete','tr')is not null)
    drop trigger tgr_student_delete
go
create trigger tgr_student_delete
on student
    for delete
as
    if(exists(select * from sys.databases where name = 'studentBackup'))
        insert into studentBackup select name,sex,age,mail from deleted;
    else
        print '不存在，创建再插入'
        create table studentBackup
        (
            name varchar(20),
            sex char(2),
            age int,
            mail varchar(20)
        )
        insert into studentBackup select name,sex,age,mail from deleted;

delete from student

select * from student
select * from studentBackup

创建instead of触发器

if(object_id('tgr_student_inteadof','tr') is not null)
    drop trigger tgr_student_inteadof
go
create trigger tgr_student_inteadof
on student
    instead of delete -- update , insert
as
    declare @id int,@name varchar(20)
    select @id = id,@name = name from deleted;
    --instead of 先触发
    --先删除user表信息
    delete from [user] where userName = @name;
    --再删除student表信息
    delete from [student] where id = @id;

delete from student where name = '海盗船长'

修改触发器

alter trigger tgr_test_insert
on student
for insert
as
print 'ok'

禁用触发器

disable trigger tgr_test_insert on student

启用触发器

enable trigger tgr_test_insert on student

查询已创建的触发器

select * from sys.triggers