Solved

DB2: V5R3M0 : trigger before update code

Posted on 2011-03-24
6
671 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 300 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 200 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Domino Lotusscript Scheduled Agent Tranfer File from AS400 to another 6 130
IBM flash storage 840 15 71
AS400  SNDDST Truncating 5 113
iSeries - Library Change Date 2 121
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 (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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

732 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