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 李四 [email protected] 上海
3 王五 [email protected], 广州如果我做修改时:
UPDATE student SET EMAIL = '[email protected]' WHERE (id = 1)
触发器则没做出动作
是否小弟关于修改的定义有问题? If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
还望各位请教,谢谢
如果在下说的不明白,或者问的比较小白,请指出,在线等,谢了先~~~
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 李四 [email protected] 上海
3 王五 [email protected], 广州如果我做修改时:
UPDATE student SET EMAIL = '[email protected]' WHERE (id = 1)
触发器则没做出动作
是否小弟关于修改的定义有问题? If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
还望各位请教,谢谢
如果在下说的不明白,或者问的比较小白,请指出,在线等,谢了先~~~
create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列
...
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
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