CODEWITHSUNDEEP
sqloracle11goracle-apexplsqldeveloper
Michael Pilapil
Michael Pilapil

Reputation: 11

TRIGGER with INSERT AND UPDATE

I need help on creating a trigger on my table1 that will insert specific columns into table2 from table1. How can I do that? I am using Oracle 11G XE .

Here is my code:

create or replace trigger AllowanceTrigger
after insert on ex_bulacan
for each row
begin
    insert into allowance (PLANT_ORIGIN,SO_NO, SO_STATUS,SO_REMARKS,DRIVER_NAME)
    values (:new.plant_origin, :new.sales_order_no, :new.status,:new.remarks, :new.driver_name);
end; ​

What if they update the data in ex_bulacan? Will automatically change my allowance table as well?

Upvotes: 1

Views: 7859

Answers (3)

Justin Cave
Justin Cave

Reputation: 231661

No. If you want the trigger to fire when there are updates to ex_bulacan, you would need the trigger to be defined both after insert and after update. If you want to update the allowance table, you'd want something like

create or replace trigger AllowanceTrigger
after insert or update on ex_bulacan
for each row
begin
  if( inserting )
  then
    insert into allowance (PLANT_ORIGIN,
                           SO_NO, 
                           SO_STATUS,
                           SO_REMARKS,
                           DRIVER_NAME)
    values (:new.plant_origin, 
            :new.sales_order_no, 
            :new.status,
            :new.remarks, 
            :new.driver_name);
  end if;

  if( updating )
  then
    update allowance
       set plant_origin = :new.plant_origin,
           so_status = :new.status,
           so_remarks = :new.remarks,
           driver_name = :new.driver_name
     where so_no = :new.sales_order_no;
  end if;
end; ​

That said, having two tables with the same set of data strongly implies that you've got a problem with improper normalization. It would generally be much better to fix the data model rather than trying to keep the data in two tables in sync via triggers.

Upvotes: 3

Iridio
Iridio

Reputation: 9271

As stated in the comments from @e-bacho 2.0 nothing will happen during update. You must specify a trigger also for the after update event. or implment your actual trigger to handle also the update

Upvotes: 0

Matt Harrison
Matt Harrison

Reputation: 358

No, you would need a trigger defined like this:

create or replace trigger AllowanceTrigger after insert or update.....

or you could create two triggers - one for insert, and one for update

Upvotes: 0

Related Questions