Solved

DB2: V5R3M0 : trigger before update code

Posted on 2011-03-24
6
650 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
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
Comment Utility
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:daveslash
daveslash earned 200 total points
Comment Utility
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:daveslash
Comment Utility

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now