CODEWITHSUNDEEP
oracle-databaseoracle11g
Chrismage
Chrismage

Reputation: 129

Truncate table with a trigger

I want to create a trigger that after insert on another table it truncates another and after that inserts the data from the first table into the truncated one. I have managed the insert part but i don't know how to incorporate the truncate command into the same trigger.

CREATE OR REPLACE TRIGGER TEST_TRIGGER AFTER INSERT ON TEST

FOR EACH ROW
BEGIN

    INSERT INTO TEST2 
       (col1, col2, col3) 
    VALUES 
       (:NEW.col1, :NEW.col2, :NEW.col3);
END;

Upvotes: 0

Views: 8644

Answers (4)

Zoghlemi Chiheb Edine
Zoghlemi Chiheb Edine

Reputation: 11

CREATE OR REPLACE TRIGGER TEST_TRIGGER AFTER INSERT ON TEST

FOR EACH ROW
BEGIN
    DELETE FROM TEST2 WHERE true ; 
    INSERT INTO TEST2 
       (col1, col2, col3) 
    VALUES 
       (:NEW.col1, :NEW.col2, :NEW.col3);
END;

Upvotes: 0

Sandeep
Sandeep

Reputation: 806

Below is the snipped using which you can achieve your goal

create table table1 (id int, age number,name varchar2(100));
create table table2 (id int, age number,name varchar2(100));

create or replace trigger sandeeptest after insert on table1 for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'truncate table  table2';
insert into table2(id,age,name) values (:new.id,:new.age,:new.name);
commit;
end;

insert into table1 (id,age,name) values (1,21,'A');
commit;    -- after commit data is persisted in both the tables

insert into table1 (id,age,name) values (2,21,'B');
rollback;-- even after rollback data is presisted in table2 because we created an autonomous trigger

select * from table1;
select * from table2;

Upvotes: 2

Himanshu
Himanshu

Reputation: 91

Surely using transactional statement inside trigger is not a good idea. But i guess you are in some practical and more significant use of it.

If you use DELETE inside trigger instead of TRUNCATE, will have

  • slow performance in case of large table
  • Most important, you will have to COMMIT which is again not allowed unless you use PRAGMA AUTONOMOUS_TRANSACTION

If you create two procedure , one to INSERT and another to TRUNCATE,will have:

  • since procedure is having commit(implicit or external), trigger needs to be defined PRAGMA AUTONOMOUS_TRANSACTION (which is again needed)

BUT think more than twice before making it autonomous. It is double sided sword which may hurt your business logic in case of rollback because you can not rollback DDL statement.

I would suggest to use dbms_job and submit procedure to it.

Upvotes: 1

CompEng
CompEng

Reputation: 7376

you can create procedure on target schema which you want to truncate table on it.

the proc like this:

CREATE OR REPLACE procedure target_schema.pr_truncate_table(p_table_name varchar2) is
begin

     execute immediate 'truncate table ' || p_table_name;


 end;
/

then you can use it on trigger.

Upvotes: 1

Related Questions