Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

update row and insert into another table with trigger.

mlov83Aug 6 2012 — edited Aug 6 2012
Hi guys i have a table that looks like this.
CREATE TABLE TEST
  (
    "COL1" VARCHAR2(20 BYTE),
    "COL2" VARCHAR2(20 BYTE),
    "COL3" VARCHAR2(20 BYTE)
  )
im going to insert values to col1 and col2 but i need get the value for column 3 from table 2 and then insert the full record into table 3. I like to do this with a trigger. I keep getting an error and i cant for the life of me figuere out what im doing wrong.
so here is my non working trigger.


here is table two

CREATE TABLE TESTTABLE
  (
    "COLUMN1" NUMBER(15,0),
    "COLUMN2" NUMBER(15,0)
  )
 

Insert into TESTTABLE (COLUMN1,COLUMN2) values (1,5);
HERE IS THE TRIGGER CODE
CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER INSERT ON TEST 
FOR EACH ROW 
BEGIN
  update TEST
  SET COL3 = (SELECT COLUMN2 FROM TEST, TESTTABLE WHERE COLUMN1 = :new.COL1);
END
I'm getting a mutating trigger error i have tried other iterations of the above but im not getting anywhere. I have dumb down my problem to these 3 table.
Can anyone point me in the right direction.
This post has been answered by Hoek on Aug 6 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 3 2012
Added on Aug 6 2012
8 comments
2,851 views