有两张表bd_psndoc,bd_psnbakdoc;
bd_psndoc中有pk_psndoc bd_name,bd_address
bd_psnbakdoc中有pk_psndoc bd_bakname,bd_bakaddress;
现在创建一个触发器 使得在bd_psnbakdoc中只要 bd_bakname,bd_bakaddress 字段修改那么再bd_psndoc中bd_name,bd_address字段得值也要随之修改(即把bd_psnbakdoc表中bd_bakname,bd_bakaddress 字段分别赋值到bd_psndoc表中得bd_name,bd_address)
bd_psndoc中有pk_psndoc bd_name,bd_address
bd_psnbakdoc中有pk_psndoc bd_bakname,bd_bakaddress;
现在创建一个触发器 使得在bd_psnbakdoc中只要 bd_bakname,bd_bakaddress 字段修改那么再bd_psndoc中bd_name,bd_address字段得值也要随之修改(即把bd_psnbakdoc表中bd_bakname,bd_bakaddress 字段分别赋值到bd_psndoc表中得bd_name,bd_address)
update ...
elseif new.bd_bakaddress<>old.bd_bakaddress then
update
end if
after
insert on "BD_PSNBASDOC" begin
update bd_psnbasdoc
set bd_psnbasdoc.bd_bakname=bd_psndoc.bd_name,
bd_psnbasdoc.bd_bakaddress=bd_psndoc.bd_address
where bd_psnbasdoc.pk_psndoc =bd_psndoc.pk_psndoc
end;
ALTER TRIGGER "GGJ"."BASGROUPDEF6" ENABLE;
update d_psndoc
set bd_name=:new.bd_bakname
where pk_psndoc=:new.pk_psndoc;
elseIF UPDATING ('bd_bakaddress') AND (:old.bd_bakaddress<> :new.bd_bakaddress) THEN
update d_psndoc
set bd_address=:new.bd_bakaddress
where pk_psndoc=:new.pk_psndoc
else
null;
END IF;EXCEPTION
WHEN others THEN
null;
END;
你的UPDATE语句不对,并且你的触发器是只有insert起作用
即把bd_psnbakdoc表中bd_bakname,bd_bakaddress 字段分别赋值到bd_psndoc表中得bd_name,bd_address那您能不能帮我改改我写的那个sql 谢了
改一下这个SQL就好了,自己好好学习改一下!这个简单的SQL也要写人帮忙来改,太....
不过看你的UPDATE语句,是和你的描述相反
如果是有INSERT语句,那写法不一样
可以用merge语句
或者先DELETE BAK表里的数据,再INSERT
从效率上来说,用MERGE好
if inserting then
insert into ....
end ifif updateing then
update
end if
如果是对bd_psndoc表UPDATE相关行,那么两个表的关联条件是什么?
就可以用:
if inserting then
insert into ac(:new.a01,:new.a02,:new.a03);
elsif updating then
update ac set b02=:new.a02,b03=:new.a03 where b01=:old.a01;
elsif deleting then
delete from ac where b01=:old.a01;
end
insert into ac(:new.a01,:new.a02,:new.a03);
elsif updating then
update ac set b02=:new.a02,b03=:new.a03 where b01=:old.a01;
elsif deleting then
delete from ac where b01=:old.a01;
end
这个方法还是不行啊 主键不能这样插进去啊,他的主键是有一个算法的,但是我不知道,主键只是用于关联这两张表得啊,这个方法行不通。看看能不能用别的什么方法
AFTER insert or update or delete ON bd_psnbakdoc
FOR EACH ROW
BEGIN
if inserting then
insert into bd_psndoc
(:new.pk_psndoc :new.bd_bakname, :new.bd_bakaddress);
elsif updating then
update bd_psndoc
set bd_name = :new.bd_bakname, bd_address = :new.bd_bakaddress
where pk_psndoc = :old.pk_psndoc;
elsif deleting then
delete from bd_psndoc where pk_psndoc = :old.pk_psndoc;
end END;
CREATE OR REPLACE TRIGGER TR_bd_psnbakdoc
AFTER insert or update or delete ON bd_psnbakdoc
FOR EACH ROW
BEGIN
if inserting then
insert into bd_psndoc
(bd_name, bd_address)
values
(:new.bd_bakname, :new.bd_bakaddress);
elsif updating then
update bd_psndoc
set bd_name = :new.bd_bakname, bd_address = :new.bd_bakaddress
where pk_psndoc = :old.pk_psndoc;
elsif deleting then
delete from bd_psndoc where pk_psndoc = :old.pk_psndoc;
end END;
update bd_psndoc
set bd_name = :new.bd_bakname, bd_address = :new.bd_bakaddress
where pk_psndoc = :old.pk_psndoc;
elsif deleting then那这个where条件 pk_psndoc = :old.pk_psndoc;
怎么获取啊