• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

原先值由空变为非空,触发器 update却无动作。各位帮我瞧瞧~

benly323 2008-04-02 10:14:40
If not exists(Select 1 from deleted) ----Insert
Begin
......
End
If not exists(select 1 from inserted) ---Delete
Begin
......
End
If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
Begin
......
End

以上是触发器关于 Insert,Delete,Update的定义,但是我当我修改表中信息时,如果内容是空的,Update的触发则无反应。

比如说表A中:
id NAME EMAIL CITY
1 张三 <NULL> 北京
2 李四 lisi@sohu.com 上海
3 王五 wangwu@sina.com, 广州

如果我做修改时:
UPDATE student SET EMAIL = 'zhangsan@163.com' WHERE (id = 1)
触发器则没做出动作
是否小弟关于修改的定义有问题? If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
还望各位请教,谢谢
如果在下说的不明白,或者问的比较小白,请指出,在线等,谢了先~~~
...全文
50 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
benly323 2008-04-02
嗯,谢谢各位,问题解决了,也大受启发,呵呵
回复
bqb 2008-04-02
create trigger t_student on student
after delete,insert,update
as
begin
delete from A where id in (select id from deleted)

if not exists (select 1 from A a,inserted i where a.id=i.id)
insert into A(id,NAME,EMAIL,CITY)
select id,NAME,EMAIL,CITY from inserted
else
update a set a.EMAIL=i.EMAIL from A a,inserted i where a.id=i.id
end
回复
wangxuelid 2008-04-02

create table bb
(id int identity(1,1) primary key,
num int )

create table aa
(id int identity(1,1)primary key,
num int )

insert into aa values(1)
insert into aa values(2)
insert into aa values(3)

insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
select * from aa
select * from bb


create trigger tr
on bb
for delete
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end
Else
Begin
insert into bb values(113)
End


select * from aa
select * from bb
回复
-狙击手- 2008-04-02
if update()
...
回复
laowan688 2008-04-02
检查是否更新了某一列,用于 insert 或 update。例:
create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-02 10:14
社区公告
暂无公告