I'm trying to create a trigger that will do the following.
After insert on Table A, query Table B based on an id (TableA.id=TableB.id) and insert corresponding info into TableA
I have a feeling I'm way off so far so any help would be appreciated
CREATE OR REPLACE TRIGGER myTrig
AFTER INSERT
ON TABLEA
BEGIN
INSERT INTO TABLEA
SELECT TABLEB.FIRST_NAME, TABLEB.LAST_NAME, SYSDATE
FROM TABLEA JOIN TABLEB ON
TABLEA.STUDENT_ID=TABLEB.STUDENT_ID
insert into TABLEA values (....);
END;
This will not work. Just think about what you are asking. You want a trigger that fires when a row is inserted into tableA to insert a row into tableA. When would the trigger stop firing?
Oracle is smart enough to step in and prevent the trigger spiralling into infinity:
SQL> create or replace trigger t69_after_ins
2 after insert on t69
3 begin
4 insert into t69 values ('blah', 'blah', 99);
5 end;
6 /
Trigger created.
SQL>
Here's what happens:
SQL> insert into t69 values ('this', 'that', 1)
2 /
insert into t69 values ('this', 'that', 1)
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_I
SQL>
"Is there any way that I can update TableA based on info from TableB given an id that is in both tables? "
If by "update" you actually do mean UPDATE, and - crucially - depending on the precise logic you wish to implement then perhaps yes:
SQL> create or replace trigger t69_after_ins
2 after insert on t69
3 begin
4 update t69
5 set name = ( select name from t23
6 where t23.id = t69.id )
7 where name is null;
8 end;
9 /
Trigger created.
SQL> insert into t69 (id, name) values (122, null)
2 /
1 row created.
SQL> select name from t69
2 where id = 122
3 /
NAME
----------
MAISIE
SQL>
However this remains a bad idea. Triggers are hard to understand and can have a deleterious nimpact on the performance of our SQL. So I suggest you try to figure out a way of building your logic into the main body of your application, rather than trying to use a trigger.
