Creating AFTER INSERT TRIGGER on Oracle table

1
votes

I don’t know how to create AFTER INSERT TRIGGER with the condition Table_1.Table_2_ID = Table_2.ID .

CREATE OR REPLACE TRIGGER Table_2_TRG
   AFTER INSERT
   ON Table_1
   FOR EACH ROW
BEGIN
    UPDATE Table_2
    SET Col1 = '1'
    FROM (SELECT Table_2_ID FROM Table_1)
    WHERE Table_1.Table_2_ID = Table_2.ID;
END;

It is needed to change record’s value in the Table_2 AFTER INSERT a record in the Table_1 with the similar ID (Table_2_ID in the Table_1 and ID in the Table_2 are identical).

Col1 in the Table_2 has type VARCHAR2.

I have this code, but after running statement there is an error:

“2/5 PL/SQL: SQL Statement ignored

4/5 PL/SQL: ORA-00933: SQL command not properly ended”

2
oracleoracle11gdatabase-trigger

2 Answers

1
votes

If I got it right, you need such a WHERE clause:

CREATE OR REPLACE TRIGGER Table_2_TRG
   AFTER INSERT
   ON Table_1
   FOR EACH ROW
BEGIN
    UPDATE Table_2 b
    SET b.Col1 = '1'
    WHERE b.id = :new.table_2_id;
END;
1
votes

this will work:

CREATE OR REPLACE TRIGGER Table_2_TRG
   AFTER INSERT
   ON Table_1
   FOR EACH ROW
BEGIN
    UPDATE Table_2
    SET Col1 = '1'
    where id=:new.id;
END;