delete
if (object_id('tgr_client_delete', 'tr') is not null)
drop trigger tgr_client_delete
go
create trigger tgr_client_delete
on clientinfo
for delete --删除触发
as
--定义变量
declare @class varchar(255), @name varchar(255);
--在deleted表中查询已经删除记录信息
select @class = clientclass,@name = clientname from deleted;
if @class=N'科室'
begin
if object_id(@name,N'U') is not null
begin
execute('drop table '+@name)
end
end
go
--插入数据
insert into clientinfo(ip,clientname,clientclass,flag) values('1.1.1.1','中国','科室',0)
update
if (object_id('tgr_client_update', 'tr') is not null)
drop trigger tgr_client_update
go
create trigger tgr_client_update
on clientinfo
for update --更新触发
as
--定义变量
declare @class varchar(255), @newname varchar(255),@oldname varchar(255);
select @class = clientclass,@oldname = clientname from deleted;
if @class=N'科室'
begin
if object_id(@oldname,N'U') is not null
begin
select @newname=clientname from inserted
execute sp_rename @oldname,@newname
end
end
go
--插入数据
insert into clientinfo(ip,clientname,clientclass,flag) values('1.1.1.1','中国','科室',0)