?
Solved

DB2: V5R3M0 : trigger before update code

Posted on 2011-03-24
6
Medium Priority
?
684 Views
Last Modified: 2012-05-11
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
Comment
Question by:bobdylan75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1200 total points
ID: 35207716
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
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 800 total points
ID: 35207754
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 35207936

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
Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

 
LVL 27

Expert Comment

by:tliotta
ID: 35209407
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
 

Author Comment

by:bobdylan75
ID: 35241453
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
 

Author Closing Comment

by:bobdylan75
ID: 35241471
thanks
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question