CODEWITHSUNDEEP
sqloracle-database
Srinivasan
Srinivasan

Reputation: 12040

Oracle trigger inserted value needs be deleted on the triggered table immediately

I have written one trigger for table T1, INSERT event, which will insert same row in another table(T2). But my requirement is, after inserting records in T2, it should be deleted from T1. Could you please suggest..

Upvotes: 0

Views: 63

Answers (1)

Sebas
Sebas

Reputation: 21522

I suggest you do the following:

1- Create a procedure to insert into t2:

CREATE OR REPLACE PROCEDURE yourname (IN values YOURTYPE) IS 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO T2 (...) VALUES (...);
    COMMIT;
END
/

2- Call this function within the AFTER INSERT trigger of T1

3- Rollback the transaction in the trigger after that. First insert should be canceled.

Please note it would compromise any other previous operation of the transaction.

Upvotes: 2

Related Questions