Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DB2: V5R3M0 : trigger before update code

Posted on 2011-03-24
6
Medium Priority
?
689 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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