参考我刚才写的,下面只是对符合更改要求且 userid>3才能触发--建表
create table userlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
create table newsuserlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
--插入数据
insert into userlist values('旺东',1,'三等功哈')
insert into userlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into userlist values('王五',1,'阿瑟风大送分')
insert into userlist values('张三',0,'阿 更好发福')
insert into userlist values('李四',1,'黄澄澄复活')insert into newsuserlist values('旺东',1,'三等功哈')
insert into newsuserlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into newsuserlist values('王五',1,'阿瑟风大送分')
insert into newsuserlist values('张三',0,'阿 更好发福')
insert into newsuserlist values('李四',1,'黄澄澄复活')
go
--建立触发器
alter trigger tr_updatename on userlist
after update
as
begin
-- print '已经触发'
if @@rowcount=0
return --没有影响,则退出
if update(username)
update newsuserlist set username=deleted.username
from deleted
where deleted.userid=newsuserlist.userid and deleted.userid>3 --这里条件
end
go--更新语句
update userlist set adrress='阿瑟风大送分'
where userid=3select * from userlist
select * from newsuserlist--删除测试环境
drop trigger tr_updatename
drop table userlist,newsuserlist
create table userlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
create table newsuserlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
--插入数据
insert into userlist values('旺东',1,'三等功哈')
insert into userlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into userlist values('王五',1,'阿瑟风大送分')
insert into userlist values('张三',0,'阿 更好发福')
insert into userlist values('李四',1,'黄澄澄复活')insert into newsuserlist values('旺东',1,'三等功哈')
insert into newsuserlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into newsuserlist values('王五',1,'阿瑟风大送分')
insert into newsuserlist values('张三',0,'阿 更好发福')
insert into newsuserlist values('李四',1,'黄澄澄复活')
go
--建立触发器
alter trigger tr_updatename on userlist
after update
as
begin
-- print '已经触发'
if @@rowcount=0
return --没有影响,则退出
if update(username)
update newsuserlist set username=deleted.username
from deleted
where deleted.userid=newsuserlist.userid and deleted.userid>3 --这里条件
end
go--更新语句
update userlist set adrress='阿瑟风大送分'
where userid=3select * from userlist
select * from newsuserlist--删除测试环境
drop trigger tr_updatename
drop table userlist,newsuserlist
你只能在触发器里先判断age是否大于18,然后决定是否执行相应的语句
应该就可以了。没测试的。
如果否,那么先禁用触发器(alter table A disable trigger 触发器名)
on A for update
as
if exists(select 1
from inserted
where age>18
)
rollback transaction
else
begin
.....
end
go
修改一条记录时,若修改后的记录的age>18时,触发器激活,提示警告!
删除一条记录时,若删除的这条记录的age>18时,触发器激活,提示警告!
create trigger tr1
on a
for insert,update,delete
as
if exists(select id from inserted ) and not exists (select id from deleted)
begin
if exists(select id from inserted where id>15)
begin
print '你插的这条记录年龄大于18'
end
endif exists(select id from inserted ) and exists (select id from deleted)
begin
if exists(select id from inserted where id>15)
begin
print '你修改后的这条记录年龄大于18'
end
endif exists(select id from deleted ) and not exists (select id from inserted)
begin
if exists(select id from deleted where id>15)
begin
print '你删除的这条记录年龄大于18'
end
end
你只能在触发器里先判断age是否大于18,然后决定是否执行相应的语句
---------------------------------------------------------------
我想也是这样的
或者你在应用程序里判断age是否大于18
如果否,那么先禁用触发器(alter table A disable trigger 触发器名)
----------------------------------------------------------------
这样不太可行吧,程序中禁用和启用触发器花费的开销应该是很大的
这样即使可以,未免效率也太低了