create trigger tr on tb instead of insert as insert into tb select 币别,金额,(case when 币别 = '人名币' then 金额 else 0 end) [人民币金额], (case when 币别 = '美元' then 金额 else 0 end) [美元金额] from instead go
create trigger tr on tb for update as update a set a.[人民币金额] = (case when b.币别 = '人名币' then b.金额 else 0 end) , a.[美元金额] = (case when b.币别 = '美元' then b.金额 else 0 end) from tb a join instead b on a.id = b.id --标识列
create trigger tr on tb after update as update a set a.[人民币金额] = (case when b.币别 = '人名币' then b.金额 else 0 end) , a.[美元金额] = (case when b.币别 = '美元' then b.金额 else 0 end) from tb a,instead b on a.id = b.id
create trigger tr on tb after update as begin if update([人民币金额]) update a set a.[人民币金额] = b.金额,a.[美元金额]=0 from tb a,instead b where a.id = b.id if update([美元金额]) update a set a.[美元金额] = b.金额,a.[人民币金额]=0 from tb a,instead b where a.id = b.id end
删除的好像不好加吧,除非放在另外的表中; 修改的只需要加上update,最好是将数据放到别的表中,否则更新还是有些问题的; 因为同一个表时Update会触发触发器,触发器在触发Update语句然后会出现循环触发的情形。create trigger tr on tb instead of update as update tc set 金额=(case when 币别 = '人名币' then 金额 else 0 end) [人民币金额], (case when 币别 = '美元' then 金额 else 0 end) from instead where tc.id in (select id from instead) go
create trigger tr on tb
instead of insert
as
insert into tb
select 币别,金额,(case when 币别 = '人名币' then 金额 else 0 end) [人民币金额],
(case when 币别 = '美元' then 金额 else 0 end) [美元金额]
from instead
go
create trigger tr on tb
for update
as
update a
set a.[人民币金额] = (case when b.币别 = '人名币' then b.金额 else 0 end) ,
a.[美元金额] = (case when b.币别 = '美元' then b.金额 else 0 end)
from tb a join instead b on a.id = b.id --标识列
after update
as
update a
set a.[人民币金额] = (case when b.币别 = '人名币' then b.金额 else 0 end) ,
a.[美元金额] = (case when b.币别 = '美元' then b.金额 else 0 end)
from tb a,instead b on a.id = b.id
after update
as
begin
if update([人民币金额])
update a set a.[人民币金额] = b.金额,a.[美元金额]=0
from tb a,instead b where a.id = b.id
if update([美元金额])
update a set a.[美元金额] = b.金额,a.[人民币金额]=0
from tb a,instead b where a.id = b.id
end
修改的只需要加上update,最好是将数据放到别的表中,否则更新还是有些问题的;
因为同一个表时Update会触发触发器,触发器在触发Update语句然后会出现循环触发的情形。create trigger tr on tb instead of update as update tc set 金额=(case when 币别 = '人名币' then 金额 else 0 end) [人民币金额], (case when 币别 = '美元' then 金额 else 0 end) from instead
where tc.id in (select id from instead)
go
if update([美元金额])
这两个数据库报错