Oracle Trigger errors

oracletrigger

I have a tables ENV_CONFIG and ENV_CONFIG_HIST where the latter table should get a new record on any insertion to the former. The trigger seems pretty straight forward but I'm getting compilation errors:

line 5 : SQL Statement ignored (Insert …)
line 13 : Column not allowed here (,new.PARAM_VALUE)

What am I missing?

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
  AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW 
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    new.PARAM_NAME
   ,new.PARAM_DESCR
   ,new.PARAM_VALUE
 );
END;

Best Answer

you just need add Colon character (:) before new keyword same as below query:

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW 
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    :new.PARAM_NAME
   ,:new.PARAM_DESCR
   ,:new.PARAM_VALUE
 );
END;

Related Solutions

Mysql – Trouble with triggers and insert .. on duplicate key update

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Mysql – Trouble with triggers and insert .. on duplicate key update

How to commit transaction on an after update event trigger

Related Question