Using INSERT INTO... SELECT in TRIGGER

Question

The question I am going to ask is already there. But I don't have answer for this.

Please refer the below link. ORACLE TRIGGER INSERT INTO ... (SELECT * ...)

I have around 600 columns in a table. After each insert in this table I need to insert the new row in another backup table.

Please tell how to use "INSERT INTO TABLE_NAME2 SELECT * FROM TABLE_NAME1" query in trigger.

Note: Without specifying columns in insert or select clause

Structure of both table is same. Specifying all the column name in trigger is difficult and also if new columns added, we need to add in trigger as well.

Possible duplicate of ORACLE TRIGGER INSERT INTO ... (SELECT * ...) — mustaccio
– mustaccio, Commented Feb 2, 2017 at 13:14
Maybe making a procedure to insert data in table 2 would be more easy? — Tenzin
– Tenzin, Commented Feb 2, 2017 at 13:25
@ Tenzin - No, this insert is via webpage. So package won't applicable here. — CHANDRU S
– CHANDRU S, Commented Feb 2, 2017 at 13:42
@ Krzosik - Around 600 columns there.Do you want me to put all the column names? — CHANDRU S
– CHANDRU S, Commented Feb 2, 2017 at 13:42
This would be a non-issue if the business logic was in the DB and you just called a procedure to insert the table_1 record. That procedure would then implement the business logic of 'when table_1 record created a backup is created in table_2'. It would be easy and the logic for the business data would be with the business data. the trigger is just a work around for having the business logic spread out and in the wrong place. — BriteSponge
– BriteSponge, Commented Feb 2, 2017 at 14:30

Elias · Accepted Answer · 2017-02-02 13:50:16Z

0

    SQL> CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
insert into emp_backup values (:new.empid, :new.fname, :new.lname);
DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table');
END;

reference: http://www.tech-recipes.com/rx/19839/oracle-using-the-after-insert-and-after-update-triggers/

answered Feb 2, 2017 at 13:50

Elias

6942 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CHANDRU S Over a year ago

Here you are mentioning only 3 columns. I am asking for without mentioning the columns, as I have huge number of columns in a single table. So planned to use select * from.

Tenzin Over a year ago

You need to write out the columns at least one time.

porgo · Accepted Answer · 2017-02-02 14:00:51Z

-2

You should use COMPOUND TRIGGER. This trigger should look like this:

CREATE OR REPLACE TRIGGER t_copy_table1
FOR INSERT ON table1
COMPOUND TRIGGER
    v_id number;

BEFORE EACH ROW IS
BEGIN
    v_id := :new.id;
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN 
    insert into table2 select * from table1 where id=v_id;
END AFTER STATEMENT;
END t_copy_table1;

edited Feb 2, 2017 at 14:00

answered Feb 2, 2017 at 13:55

porgo

1,7513 gold badges19 silver badges30 bronze badges

4 Comments

Marco Polo Over a year ago

This would only work for a single INSERT into the main table. If there is an INSERT AS SELECT done on the main table, only the last row will be copied to the backup table. You would need to introduce a PL-SQL table to store the IDs and the you run into memory consideration problems.

porgo Over a year ago

@MarcoPolo OK, but there is not said that insert as select will be done for this table. For single insert is proper way to do this, I think.

CHANDRU S Over a year ago

One more condition here is, The id you mentioned in the above trigger may have duplicates. But i need only the last entry inserted newly in the first table.

porgo Over a year ago

@CHANDRUS so use a column which is primary key for this table, I use id only for showing example

Collectives™ on Stack Overflow

Using INSERT INTO... SELECT in TRIGGER

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related