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;
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 ...;
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.
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
insertwithout specifying a column list is considered bad coding style. As is usingselect *