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.