Trigger to automatically add values to a table in Oracle

Question

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?

Answer 1

This 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>

Answer 2

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;