SQL & PL/SQL

Hi all:

Good day…

I hit a bottleneck where my insert trigger won’t execute the insert statement (with subquery). Please see illustration below:

Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))

Step# 2 – manipulated the tables:
Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:

CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
.....
BEGIN
INSERT INTO TABLE_B(b1,b2,b3)
(select c1, c2, c3
from TABLE_C);
exception
when others then
dbms_output.put_line ('ERROR');
END TABLE_A_TR;

Step# 3 – compiled the created trigger and I’ve successfully compiled it.
Step# 4 – Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,’testa’,’testb’)

I’ve successfully insert the values into TABLE_A however I’ve observed that the trigger didn’t execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there’s an issue in my insert statement but I’ve successfully populated the values into TABLE_B. So I’m wondering why the trigger didn’t execute the insert statement. Are there are any missing steps or syntax? Is this a limitation to oracle triggers? Please shed some light here. Thank you in advance.

Take care,
Jan