Hello Experts! I want to insert value to second table instead of first table using trigger

userw8wh1 0
Viewed 19

Could you help me to complete my work. Because I'm a fresher to Oracle Technology.

I want to insert value to second table instead of first table using trigger. If I try to insert any value in first table, It will insert into second table instead of first table. Please help me with this. Thanks to all. Here is the sample data

create table table_1(id number); create table table_2(id number); I'm going to insert value in table_1 like insert into table_1 values(10);

select * from table_2;

Sample O/P: id

10

select * from table_1;

O/P: id

plsqldeveloper plsql oracle sql
1 Answers

Basically, you'd have table_1 empty in all cases, and all data stored in table_2. To do that, you'll need two triggers because row-level trigger ("for each row") can't remove data from its base table (because that table is mutating).

Sample tables:

SQL> create table table_1(id number);

Table created.

SQL> create table table_2(id number);

Table created.

Row level trigger which "redirects" data into table_2:

SQL> create or replace trigger trg_bi_1
  2    before insert on table_1
  3    for each row
  4  begin
  5    insert into table_2 (id) values (:new.id);
  6  end;
  7  /

Trigger created.

Statement level trigger which removes data from table_1:

SQL> create or replace trigger trg_ai_1
  2    after insert on table_1
  3  begin
  4    delete from table_1;
  5  end;
  6  /

Trigger created.

Testing:

SQL> insert into table_1 (id) values (10);

1 row created.

SQL> select * from table_1;

no rows selected

SQL> select * from table_2;

        ID
----------
        10

SQL>
Littlefoot0
Related