关于Sqlserver触发器的一些操作

一、触发器的基本操作

--创建
create trigger [triggerName] on [tableName]
for [insert/update/delete]
as 
begin
[操作]
rollback transaction
end
go

--修改
-- 1. 修改内容
ALTER TRIGGER [triggerName] ON [tableName]
for [insert/update/delete]
as 
begin
[操作]
rollback transaction
end
go
--2. 改名
EXEC sp_rename [原名], [新名]
go

--删除触发器
drop trigger [triggerName]

--查看当前库所有的触发器
select * from Sysobjects WHERE xtype = 'TR'
--查询表的触发器
exec sp_helptrigger '[表名]'
--查询触发器的创建信息
exec sp_help '[触发器名]'


--禁用/启用 触发器
--单个
alter table [tableName] [enable/disable] trigger [triggerName];
--表的
alter table [tableName] [enable/disable] trigger all;
--服务器的
exec sp_msforeachtable 'alter table ? [enable/disable] trigger all'

二、级联操作举例

前置操作

--人员表
create table person (
	id int primary key identity(1, 1),
	name nvarchar(20)
)

--人员备份表
create table personCopy (
	id int primary key identity(1, 1),
	name nvarchar(20)
)

1. 级联添加

--添加到 person 表时，也添加到备份表 personCopy中
create trigger tri_insert_person on person
for insert 
as 
declare @name nvarchar(20)
select @name = name from inserted
insert into personCopy(name) 
values(@name)
go 

insert into person(name) values('小明')

结果：

2. 级联删除

--删除人员表 person 的信息时，把备份表personCopy的人员信息也删除
create trigger tri_delete_person on person
for delete 
as 
declare @name nvarchar(20)
select @name = name from deleted
delete from personCopy where name = @name
go

delete from person where name = '小明'

 结果：

3. 级联修改

这个比较特殊一点

--修改 person 表里面内容时，同时修改personCopy里面的内容
create trigger tri_update_person on person
for update
as
declare @oldName nvarchar(20), @name nvarchar(20)
select @oldName = deleted.name, @name = inserted.name from inserted, deleted
update personCopy set name = @name where name = @oldName
print('记录已修改')
go

update person set name = '小红' where name = '小明' 

 结果：