create table test(a int) insert into test select 0 union select 4 union select 10create 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
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 endgo
--测试环境 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 table Test ( a varchar(10), b varchar(10) )insert into Test(a,b) values('01','10')insert into Test(a,b) values('02','04')create trigger Tr_Test on Test for update as begin if(update(b)) if exists(select 1 from deleted where b='10') raiserror 50001 '不能修改10为4' endupdate test set b='04' where a='01' 多谢兄弟们的指点,已经写出来了,谢谢大家!
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 --testupdate test set a=1 where a=0update test set b=111 where a=10update test set a=1 where a=10 select * from test -- drop table test
insert into test
select 0
union select 4
union select 10create 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
-------------------
楼上的触发器有漏洞!
如果修改了该表的其它字段,刚好那条记录的a字段的值为10
按楼主的意思,其实此时是不用报错的!
要多加个条件
for update
as
if update(a)
if exists(select 1 from deleted where a=10)go
就抱错,而不是整个表里面有a列为10的行就不行,只针对当前要update的行
for update
as
if update(a)
begin
if exists(select 1 from deleted where a=10)
begin
rollback
raiserror ('不能修改a=10的值',16,1)
end
endgo
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
(
a varchar(10),
b varchar(10)
)insert into Test(a,b) values('01','10')insert into Test(a,b) values('02','04')create trigger Tr_Test on Test for update
as
begin
if(update(b))
if exists(select 1 from deleted where b='10')
raiserror 50001 '不能修改10为4'
endupdate test set b='04' where a='01'
多谢兄弟们的指点,已经写出来了,谢谢大家!
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
--testupdate test set a=1 where a=0update test set b=111 where a=10update test set a=1 where a=10
select * from test
--
drop table test