--创建测试环境 set nocount on go create table table1 ( ID int identity, name varchar(20) ) insert table1(name) select 'AAA' union select 'BBB' union select 'CCC'/* 禁止删除name为AAA的行 */ if exists(select 1 from sysobjects where id=object_id('tr') and xtype='TR') drop trigger tr go create trigger tr on table1 for delete as if exists(select 1 from deleted where name='AAA') begin set identity_insert table1 on insert table1(ID,name) select * from deleted where name='AAA' set identity_insert table1 off end go--测试 delete from table1 --查看 select * from table1drop table table1--结果 /*ID name ----------- -------------------- 1 AAA */
set nocount on
go
create table table1
(
ID int identity,
name varchar(20)
)
insert table1(name)
select 'AAA' union select 'BBB' union select 'CCC'/*
禁止删除name为AAA的行
*/
if exists(select 1 from sysobjects where id=object_id('tr') and xtype='TR')
drop trigger tr
go
create trigger tr
on table1
for delete
as
if exists(select 1 from deleted where name='AAA')
begin
set identity_insert table1 on
insert table1(ID,name) select * from deleted where name='AAA'
set identity_insert table1 off
end
go--测试
delete from table1
--查看
select * from table1drop table table1--结果
/*ID name
----------- --------------------
1 AAA
*/