CODEWITHSUNDEEP
sqloracle-databaseplsql
Eduardo Lynch Araya
Eduardo Lynch Araya

Reputation: 824

ORACLE TRIGGER INSERT INTO ... (SELECT * ...)

Trigger with Insert into (select * ...)

I'm trying it.

INSERT INTO T_ USERS SELECT * FROM USERS WHERE ID = :new.ID;

not working...

this work.

INSERT INTO T_USERS(ID) VALUES(:new.ID);

Trigger

create or replace trigger "TRI_USER"
AFTER
insert on "USER"
for each row
begin
INSERT INTO T_USER SELECT * FROM USER WHERE ID = :new.ID;
end;​

Upvotes: 3

Views: 8856

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Its not about your trigger but because of INSERT statement

here insert statement works as below

INSERT INTO <TABLE>(COL1,COL2,COL3) VALUES (VAL1,VAL2,VAL3);  --> If trying to populate value 1 by one.

INSERT INTO <TABLE>(COL1,COL2,COL3) --> If trying to insert mult vales at a time
SELECT VAL1,VAL2,VAL3 FROM <TABLE2>;

The number of values should match with number of columsn mentioned.

Hope this helps you to understand

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24569

this work.

INSERT INTO T_USERS(ID) VALUES(:new.ID);

So if it fits to you then try this:

INSERT INTO T_USER(ID) SELECT ID FROM USER WHERE ID = :new.ID;

If you want to select one or more rows from another table, you have to use this syntax:

insert into <table>(<col1>,<col2>,...,<coln>)
select <col1>,<col2>,...,<coln>
from ...;

Upvotes: 1

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Perhaps you could post the actual error you are experiencing?

Also, I suggest that you rethink your approach. Triggers that contain DML introduce all sorts of issues. Keep in mind that Oracle Database may need to restart a trigger, and could therefore execute your DML multiple times for a particular row.

Instead, put all your related DML statements together in a PL/SQL procedure and invoke that.

Upvotes: 1

Related Questions