Hi all, I have following table:
PARTITION_ID VARCHAR2(20)
ROLE_NOW VARCHAR2(CHAR)
REMOVED DATE
EDITING VARCHAR2(3)
UPDATE_DATE DATE
If EDITING column is updated with 'no' value, a trigger should update the UPDATE_DATE column with 'sysdate' value.
I get ORA-04091 error on the trigger execution. So I try to use COMPOUND TRIGGER modifing the existent trigger as follow:
CREATE OR REPLACE
TRIGGER tri_hot_active
FOR UPDATE OF editing ON tab_informations
COMPOUND TRIGGER
v_partition VARCHAR2(20);
v_get VARCHAR2(100);
v_update_sql VARCHAR2(1000);
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('inizio');
if :new.editing='no' THEN
v_partition:= :old.partition_id;
dbms_output.put_line('v_partition= '||v_partition); --debug purpose
dbms_output.put_line('v_update old= '|| :old.update_date); --debug purpose
v_update_sql:='update tab_informations set update_date=sysdate where partition_id='''||:new.partition_id||'''';
dbms_output.put_line('v_update_sql= '|| v_update_sql); --debug purpose
execute immediate v_update_sql;
end if;
END AFTER EACH ROW;
END tri_hot_active;
/
But I continue to get ORA-04091. Can you suggest me a solution?
I'm working on 11gR2.
Thanks,
Ste