I am new to Oracle.
I am looking for a trigger which helps to automatically insert values into a table when data is entered into another table in the same database.
I have tried a lot and didn't succeed. Can anyone explain how this is done?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityThis is very simple and there are many tutorials out there.
Here is a sample trigger to demonstrate.
create table aaaa
(
a number
);
create table bbbb
(
b number
);
create trigger aaaa_aitrig
after insert on aaaa
for each row
begin
insert into bbbb values ( :new.a );
end;
/
In case you were wondering, ":new
" is a reference to the newly inserted row, and each column can be referenced individually.
Test case:
SQL> select count(*) from bbbb;
COUNT(*)
----------
0
SQL> insert into aaaa values ( 1 );
1 row created.
SQL> select * from bbbb;
B
----------
1
SQL>
Sounds like you just want a basic AFTER INSERT
trigger. Then you can access the inserted data via :new
. Something like:
CREATE OR REPLACE TRIGGER insert_data
AFTER INSERT
ON OWNER.TABLE
FOR EACH ROW
BEGIN
INSERT INTO OWNER.OTHER_TABLE
(COL1,
COL2,
COL3)
VALUES
(:NEW.COL1,
:NEW,COL2,
:NEW,COL3)
END;