Error while creating trigger for add new row into table A after insert in table B in oracle

Before read the question, please check this

I want to create trigger which insert new row into table A after insert in table B. The value for attribute z (FK) on A is z (PK) on table B (so I pass new.z as the value for z on A), and for y on A, the value is y on C. So, i think the trigger would look like this:

CREATE TRIGGER add_to_A
AFTER INSERT on B
FOR EACH ROW BEGIN
    select *
        from new, B, C
        where new.z = B.z and B.t = C.t
    INSERT INTO A (x, y, z) VALUES ('1', C.y, B.z);
END

But i found it’s wrong and error. here are the errors:

Error(1): PL/SQL: SQL Statement ignored

Error(4,6): PL/SQL: ORA-00933: SQL command not properly ended

Error(5,3): PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: ; The symbol “;” was substituted for “end-of-file” to continue.

What do you think? and how do i fix it?

Answer

I think you need such an INSERT statement along with a SELECT within the trigger

CREATE OR REPLACE TRIGGER add_to_A
AFTER INSERT ON B
FOR EACH ROW 
BEGIN
    INSERT INTO A 
    SELECT myseq.nextval, y, :NEW.z 
      FROM C
     WHERE t = :NEW.t;
END;
/

where

• t,x are presumed to be the primary key columns of the tables C and A respectively
• commit is not added just after the DML statement for the sake of transaction integrity for the applications through which the data into the table B is inserted