Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Update SQL


This question is answered.


Permlink Replies: 2 - Last Post: Mar 7, 2016 2:20 AM Last Post By: Melissa Torn
Melissa Torn

Posts: 143
Registered: 4/30/09
Update SQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 5, 2016 10:32 PM
I added a new field (.UPRICELOCAL) into a table and
created a trigger for updating that new field.
Everthing is OK. and triggers works.
Now i want to update the new field values
for OLD RECORDS so i need SQL UPDATE
How can i convert below Trigger to Update SQL ?

Thank You

CREATE TRIGGER "INVDETAIL_BEFOREINS" FOR "INVDETAIL"
ACTIVE BEFORE INSERT POSITION 0

AS

declare variable TDATE DATE;
declare variable DOCCURRENCY VARCHAR(6);
declare variable USD NUMERIC(9, 4);
declare variable CHF NUMERIC(9, 4);

begin

SELECT TDATE, DOCCURRENCY FROM INVOICE
WHERE RNO=New.RNO
INTO :TDATE, :DOCCURRENCY;

SELECT USD, CHF FROM CURRRATE
WHERE TDATE=:TDATE
INTO :USD, :CHF;

IF (:DOCCURRENCY='EUR') THEN
New.UPRICELOCAL=New.UPRICE;
IF (:DOCCURRENCY='USD') THEN
New.UPRICELOCAL=New.UPRICE * :USD;
IF (:DOCCURRENCY='CHF') THEN
New.UPRICELOCAL=New.UPRICE * :CHF;
end;
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Update SQL
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 6, 2016 1:54 PM   in response to: Melissa Torn in response to: Melissa Torn
since this is a one off type of operation (once you have updated the old all the
new are going to be right) just write a quick stored procedure that loops over
all records in the table and do the same logic that is in your trigger to
determine how to update that record.

basically it would look something like (pseudo code) If RNO is not the primary
key of the INVDETAIL table, get the primary key in the loop and chang the update
statement to use that. Then just call it once and you can drop it from the DB
if you want.

...
   for select RNO, UPRICE
         from INVDETAIL
        where UPRICELOCAL is null
         into :RNO, :UPRICE do
   begin
     SELECT TDATE, DOCCURRENCY FROM INVOICE
      WHERE RNO = :RNO
       INTO :TDATE, :DOCCURRENCY;
 
     SELECT USD, CHF FROM CURRRATE
      WHERE TDATE = :TDATE
       INTO :USD, :CHF;
 
     IF (:DOCCURRENCY='EUR') THEN
       UPRICELOCAL = UPRICE;
     IF (:DOCCURRENCY='USD') THEN
       UPRICELOCAL = UPRICE * :USD;
     IF (:DOCCURRENCY='CHF') THEN
        UPRICELOCAL = UPRICE * :CHF;
 
 
     update INVDETAIL
        set UPRICELOCAL = :UPRICELOCAL
      where RNO = :RNO
   end


Melissa Torn wrote:
I added a new field (.UPRICELOCAL) into a table and
created a trigger for updating that new field.
Everthing is OK. and triggers works.
Now i want to update the new field values
for OLD RECORDS so i need SQL UPDATE
How can i convert below Trigger to Update SQL ?

Thank You

CREATE TRIGGER "INVDETAIL_BEFOREINS" FOR "INVDETAIL"
ACTIVE BEFORE INSERT POSITION 0

AS

declare variable TDATE DATE;
declare variable DOCCURRENCY VARCHAR(6);
declare variable USD NUMERIC(9, 4);
declare variable CHF NUMERIC(9, 4);

begin

SELECT TDATE, DOCCURRENCY FROM INVOICE
WHERE RNO=New.RNO
INTO :TDATE, :DOCCURRENCY;

SELECT USD, CHF FROM CURRRATE
WHERE TDATE=:TDATE
INTO :USD, :CHF;

IF (:DOCCURRENCY='EUR') THEN
New.UPRICELOCAL=New.UPRICE;
IF (:DOCCURRENCY='USD') THEN
New.UPRICELOCAL=New.UPRICE * :USD;
IF (:DOCCURRENCY='CHF') THEN
New.UPRICELOCAL=New.UPRICE * :CHF;
end;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Update SQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 7, 2016 2:20 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank you so much Jeff
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02