• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

DB2: V5R3M0 : trigger before update code

I have to create a trigger that before to update
the field QUANTITY on a table "DELIVERY", it updates a field DIFFERENCE = NewQuantity-CurrentQuantity,
and update the field WHEN with the current time and date..
Only after have done so, it can update QUANTITY with new quantity
how to do so?
thanks
0
bobdylan75
Asked:
bobdylan75
2 Solutions
 
momi_sabagCommented:
CREATE TRIGGER myTrigger
     NO CASCADE BEFORE UPDATE OF DELIVERY ON QUANTITY
     REFERENCING NEW AS NEWR OLD AS OLDR
     FOR EACH ROW
     BEGIN ATOMIC
        SET NEWR.DIFFERENCE  = NEWR.QUANTITY - OLDR.QUANTITY ;
        SET NEWR.WHEN= current timestamp;
     END
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Something like this should do the trick. HTH!

create trigger TR_DeliveryBeforeUpdate
   before update of QUANTITY on DELIVERY
   referencing new row as NewRow
               old row as OldRow
   for each row
 Begin
  UpdateIt:
    set NewRow.DIFFERENCE = NewRow.CurrentQuantity - OldRow.CurrentQuantity;
    set NewRow.someOtherColumn = current timestamp;
 End;

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Wow, it looks like Momi and I were typing at the same time. Apparently, great minds thnk alike :-)  (Notice how similar our triggers look.)

I did just notice that the main tow in my source should be changed to this:
    set NewRow.DIFFERENCE = NewRow.Quantity - OldRow.Quantity;
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tliottaCommented:
Just in case some clarification is needed...

Do you want to know how to write code for a trigger? Or do you want to know what you need to do to add a trigger to the database?

What kind of access do you have to the system? Do you have the iSeries Access product installed on your PC? Do you have the SQL Development Kit installed on your server?

Tom
0
 
bobdylan75Author Commented:
tliotta I use two access: via STRSQL command on prompt command.
Or via Operation Navigator via DB2 tools..

But I need only the code this time thnaks.
0
 
bobdylan75Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now