create or replace trigger TRI_COMP_BYQ_INFO_INSERTUPDATE
before insert OR UPDATE on t_comp_byq_info
for each row
DECLARE
updatenumber number;
-- local variables here
BEGIN IF UPDATING THEN
IF :NEW.DEPT_CODE != :OLD.DEPT_CODE THEN
updatenumber := 0;
select count(*)
into updatenumber
from T_COMP_CUSTOMER_INFO
where GDY_FLAG = '1'
and CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in
(select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID));
if(updatenumber != 0) then
update T_COMP_CUSTOMER_INFO t
set t.dept_code = :new.dept_code
where t.GDY_FLAG = '1'
and t.CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID));
end if;
END IF;
end if;
END TRI_COMP_BYQ_INFO_INSERTUPDATE;
before insert OR UPDATE on t_comp_byq_info
for each row
DECLARE
updatenumber number;
-- local variables here
BEGIN IF UPDATING THEN
IF :NEW.DEPT_CODE != :OLD.DEPT_CODE THEN
updatenumber := 0;
select count(*)
into updatenumber
from T_COMP_CUSTOMER_INFO
where GDY_FLAG = '1'
and CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in
(select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID));
if(updatenumber != 0) then
update T_COMP_CUSTOMER_INFO t
set t.dept_code = :new.dept_code
where t.GDY_FLAG = '1'
and t.CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID));
end if;
END IF;
end if;
END TRI_COMP_BYQ_INFO_INSERTUPDATE;
into updatenumber
from T_COMP_CUSTOMER_INFO
where GDY_FLAG = '1'
and CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in
(select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID)); 报错
现在的目的是在表行级修改时 我想批量修改其他的表的数据,这样可行吗?
我现在把update语句放到过程中了,但是还是执行报错
create or replace trigger TRI_COMP_BYQ_INFO_INSERTUPDATE
before insert OR UPDATE on t_comp_byq_info
for each row
DECLAREBEGIN IF INSERTING THEN
:NEW.TQ_ID := :NEW.BYQ_ID;
END IF; IF UPDATING THEN
IF :NEW.DEPT_CODE != :OLD.DEPT_CODE THEN
PRO_BYQ_INTERFACE(:NEW.BYQ_ID,:NEW.DEPT_CODE,:NEW.XIANLU_CODE,:NEW.YYZ_CODE,'1');
PRO_BYQ_INTERFACE(:NEW.BYQ_ID,:NEW.DEPT_CODE,:NEW.XIANLU_CODE,:NEW.YYZ_CODE,'2');
END IF;
IF :NEW.XIANLU_CODE != :OLD.XIANLU_CODE THEN
PRO_BYQ_INTERFACE(:NEW.BYQ_ID,:NEW.DEPT_CODE,:NEW.XIANLU_CODE,:NEW.YYZ_CODE,'3');
END IF;
IF :NEW.YYZ_CODE != :OLD.YYZ_CODE THEN
PRO_BYQ_INTERFACE(:NEW.BYQ_ID,:NEW.DEPT_CODE,:NEW.XIANLU_CODE,:NEW.YYZ_CODE,'4');
END IF;
END IF;END TRI_COMP_BYQ_INFO_INSERTUPDATE;CREATE OR REPLACE PROCEDURE PRO_BYQ_INTERFACE(BYQ_ID1 in varchar2,DEPT_CODE1 in varchar2, XIANLU_CODE1 in varchar2, YYZ_CODE1 in varchar2,OPRATE_TYPE in varchar2)
is -- OPRATE_TYPE 1:修改高压用户的部门;
-- OPRATE_TYPE 2:修改低压用户的部门;
-- OPRATE_TYPE 3:修改低压用户的线路代码;
-- OPRATE_TYPE 4:低压变压器的YYZ_CODE 修改低压用户的部门;
BEGIN
IF(OPRATE_TYPE = '1') then
update T_COMP_CUSTOMER_INFO t
set t.dept_code = dept_code1
where t.GDY_FLAG = '0'
and t.CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = BYQ_ID1));
end if;
IF(OPRATE_TYPE = '2') then
update T_COMP_CUSTOMER_INFO
set dept_code = (select dept_id
from t_code_departments
where dept_type_id = '05'
and parent_id = DEPT_CODE1
and rownum = 1)
where GDY_FLAG = '1'
and CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = BYQ_ID1));
end if;
IF(OPRATE_TYPE = '3') then
UPDATE T_COMP_CUSTOMER_INFO
set xianlu_code = xianlu_code1
where CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = BYQ_ID1));
end if;
IF(OPRATE_TYPE = '4') then
FOR x in (select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in (select tq_id
from t_comp_byq_info
where byq_id = BYQ_ID1)) loop
UPDATE T_COMP_CUSTOMER_INFO
SET DEPT_CODE = YYZ_CODE1
WHERE GDY_FLAG = '1'
and CUSTOMER_ID = x.customer_id;
end loop;
end if;
end PRO_BYQ_INTERFACE;
你这个赋值是什么意思呢?
没看明白~~
这里无关紧要
是这一行改变后,tq_id 自动对应了 byq_id
before insert OR UPDATE on t_comp_byq_info
for each row
DECLARE
updatenumber number;
-- local variables here
BEGIN IF UPDATING THEN
IF :NEW.DEPT_CODE != :OLD.DEPT_CODE THEN
updatenumber := 0;
select count(*)
into updatenumber
from T_COMP_CUSTOMER_INFO
where GDY_FLAG = '1'
and CUSTOMER_ID in
(select t.CUSTOMER_ID
from T_COMP_JLD_INFO t
where t.tq_id in
(select tq_id
from t_comp_byq_info
where byq_id = :NEW.BYQ_ID)); //触发器不能访问mutation table,也就是触发的表
...
END TRI_COMP_BYQ_INFO_INSERTUPDATE;