CODEWITHSUNDEEP
oracle-databaseplsql
user381878
user381878

Reputation: 1539

Creating a back up table trigger in Oracle

I have a table A which is constantly updated ( insert statements ) by one application. I want to create another table B which consists only few columns of table A. For this I thought of creating a trigger which triggered after insert on Table A, but I don't know how should I write the insert statement inside the trigger. I am not a database expert, so may be I am missing something simple. Please help.

Here is my trigger code:

    CREATE OR REPLACE TRIGGER "MSG_INSERT_TRIGGER" 

AFTER
insert on "ACTIVEMQ_MSGS"

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'truncate table MSG_BACKUP';
insert into MSG_BACKUP select * from ACTIVEMQ_MSGS;
COMMIT;
end;

Upvotes: 2

Views: 6909

Answers (2)

Rene
Rene

Reputation: 10541

Inside an Oracle trigger the record values are available in de :new pseudo record. Your insert statement may look like this:

insert into B (column1, column2) 
values (:new.tableA_col1,:new.tableA_col2)

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

This does not seem to be good idea: every time a new record gets inserted into Table A, delete everything from table B and copy all records from table A. This will be a huge performance issue, once there is many records in table A.

Wouldn't it be enough to create a view on the desired columns of table A?

If not, that is, you still want to "log" all inserts into an other table, then here you go. (I suppose you want to copy the following fields: f1, f2, f3)

CREATE OR REPLACE TRIGGER TR_AI_ACTIVEMQ_MSGS
AFTER INSERT ON ACTIVEMQ_MSGS
FOR EACH ROW
BEGIN
  INSERT INTO MSG_BACKUP (f1, f2, f3)
  VALUES (:new.f1, :new.f2, :new.f3);            
END TR_AI_ACTIVEMQ_MSGS;

Upvotes: 3

Related Questions