CODEWITHSUNDEEP
sqloracle-databaseplsql
José Del Valle
José Del Valle

Reputation: 691

PL/SQL trigger to insert values to another table

I'm trying to create a trigger that inserts values to a temporary table, but I also want it to delete those values from the actual table I wanted to modify, to explain myself better:

I have created this table

create table R2(a int, b date);

And a temporary table that's basically the same as R2

CREATE GLOBAL TEMPORARY TABLE R2TEMP
 AS SELECT * FROM R2;

If I do:

INSERT INTO R2 VALUES (1,'09/05/1995');

I want this trigger to insert those values in R2TEMP, instead of inserting them in R2 (I used a DELETE FROM R2 but I want to know how I can avoid it)

CREATE OR REPLACE TRIGGER BLOCK_INSERT2
BEFORE INSERT ON R2
FOR EACH ROW

BEGIN


INSERT INTO R2TEMP(A,B)
SELECT * FROM R2;

DELETE FROM R2;
END;

I want to know how could I insert those values in another table, different than the one that caused the trigger, I hope I explained myself, and thanks for your time

Upvotes: 0

Views: 188

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think you are looking for the instead of trigger (documentation here).

It is very easily coded:

CREATE OR REPLACE TRIGGER BLOCK_INSERT2
INSTEAD OF INSERT ON R2
FOR EACH ROW
BEGIN
    INSERT INTO R2TEMP(A,B)
        SELECT A, B FROM R2;
END;

I would advise you to list all the columns explicitly, instead of using select *.

Upvotes: 1

Related Questions