TRIGGER with INSERT AND UPDATE

Question

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?

It won't change when they update because your trigger is only `after insert` — Boris Bachovski, Mar 30 '12 at 02:34

Justin Cave · Answer 1 · 2012-03-30T05:32:14.117

3

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.

edited Mar 30 '12 at 05:32

answered Mar 30 '12 at 03:29

Justin Cave

227,342
24
367
384

Okay i'm gonna try your code.. what i want if the SO_NO in ex_bulacan is already in allowance table it will automaticaly update the data in allowance table.Thanks!! – Michael Pilapil Mar 30 '12 at 04:16
i got this error master please help about this"Compilation failed, line 23 (12:26:16) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers." PLS-00103: Encountered the symbol ";" when expecting one of the following: if – Michael Pilapil Mar 30 '12 at 04:27
@MichaelPilapil - Did you see the edit I made to fix a missing `END IF`? If you want to update the `allowance` table based on the `so_no`, the `WHERE` clause in the second `UPDATE` would need to reference `so_no`. I'm guessing that would mean that you'd want to `UPDATE` the `plant_origin` column as well. – Justin Cave Mar 30 '12 at 04:33
Actually Sir @JustinCave only the so_no thai will not change thats my unique key for both tables how can i do that? please help me sir.. – Michael Pilapil Mar 30 '12 at 05:16
@MichaelPilapil - Updated my answer to use `so_no` as the key rather than `plant_origin`. – Justin Cave Mar 30 '12 at 05:33
please give me the exact query for that? do i have to give parameter to IF?like if so_no = :new.sales_order_no – Michael Pilapil Mar 30 '12 at 05:33
@MichaelPilapil - I told you, I updated my answer. The current answer should, if I understand, be the exact statement you're looking for. – Justin Cave Mar 30 '12 at 05:35
i got this error when i compile your code "PLS-00103: Encountered the symbol "THEN" when expecting one of the following: ( - + case mod new not null" what will i do for this? Pardon sir please help me.. – Michael Pilapil Mar 30 '12 at 05:43
@MichaelPilapil you should mark the answer as accepted. You'll have benefits from that. And others, and the site will be better with accepted answers. – Florin Ghita Mar 30 '12 at 07:36

score 0 · Answer 2 · answered Mar 30 '12 at 02:46

0

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

answered Mar 30 '12 at 02:46

Matt Harrison

338
3
10

score 0 · Answer 3 · answered Mar 30 '12 at 02:49

0

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

answered Mar 30 '12 at 02:49

Iridio

9,213
4
49
71

the answer from Justin Cave should do what you need – Iridio Mar 30 '12 at 03:39

TRIGGER with INSERT AND UPDATE

3 Answers3