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,[email protected])

查看运行结果

创建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

 

 

本文链接:15、SQL Server 触发器,转载请注明。



You must enable javascript to see captcha here!

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress

无觅相关文章插件,快速提升流量