Conditional update script.

[RobColborne]

Hi

I have a database set up where a single row is one record consisting of Quantity and Value paired fields.

I have a currency field that when changed will trigger an update statement so that the value fields automatically get calculated by the a fixed price(hard coded) dependent of the chosen currency. So the logic would resemble somthing like:

If Currency_Field = 'GBP'
Then
Value_Field1 = Value_Qty1 * 2.00
Value_Field2 = Value_Qty2 * 4.00
.
.
Else
Field1_Value = Field1_Qty * 4.00
Field2_Value = Field2_Qty * 8.00
.
.
End If

Hope this makes sense, how would I go about designing this script?

Thanks in advance

Rob



Robert Colborne
----------------------------
Certified GoldMine, AccPac CRM and SuperOffice consultant

 

[JayKusch]

I think a CASE statementmay work

CASE Currency_Field
WHEN 'GBP'
THEN SET Value_Field1 = (Value_Qty1 * 2.00)
SET Value_Field2 = (Value_Qty2 * 4.00)
ELSE
SET Field1_Value = Field1_Qty * 4.00)
SET Field2_Value = Field2_Qty * 8.00)
END


Thanks

J. Kusch

 

[ProdAir]

do you want the column to get automatically updated every time the value changes? If you do, then you'll need to create a trigger on the table.