create table test(a int,b int)
insert into test
select 0,1
union select 4,2
union select 10,3
go
create trigger tu_test on test
for update
as
if exists(select 1 from deleted t1,inserted t2 where t1.a<>t2.a and t1.a=10)
begin
rollback
raiserror ('不能修改a=10的值',16,1)
end
go
--test
update test set a=1 where a=0
update test set b=111 where a=10
update test set a=1 where a=10
select * from test
--
drop table test
--测试环境
Create table T_Table( id int identity(1,1),a varchar(10))
insert into T_Table select '0'
union all select '04'
union all select '10'
--触发器
Create Trigger T_T_Table on T_Table
for update
as
begin
if update(a)
begin
if (select A.a from T_Table A inner join deleted D on A.id=D.ID)='10'
begin
print '修改错误'
rollback
end
end
end
--测试
update T_table set a='10'
where id=1
--显示结果
/*
修改错误
*/
--查看原表数据(未进行更新)
select * from T_table
/*
id a
----------- ----------
1 0
2 04
3 10
(所影响的行数为 3 行)
*/
--删除测试环境
Drop table T_table
Drop Trigger T_T_Table
create trigger tu_test on test
for update
as
if update(a)
begin
if exists(select 1 from deleted where a=10)
begin
rollback
raiserror ('不能修改a=10的值',16,1)
end
end
create table test(a int)
insert into test
select 0
union select 4
union select 10
create trigger tu_test on test
for update
as
if exists(select 1 from deleted where a=10)
begin
rollback
raiserror ('不能修改a=10的值',16,1)
end
go
--test
select * from test
update test set a=1 where a=0
select * from test
update test set a=1 where a=10
select * from test
--
drop table test