"Its 1.48" I said looking at the database, "but its 0.9 according to the application" I was told "and all the others are correct, its just the exchange rate for the Euro that's wrong".
Eventually we worked out that when the base currency is not the Euro you have to enter the exchange rate for the Euro against your base currency. This update the field for the base rate in the currency table but that doesn't help when I want to look up the exchange rate for the Euro.
The solution was to create a trigger that updates the Euro exchange rate when the base rate currency changes.
ALTER TRIGGER [BaseRateUpdate] ON [SYSCurrency] AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT ThisIsBaseCurrency FROM DELETED) = 1
BEGIN
IF (SELECT ThisIsEuroCurrency FROM DELETED) = 0
BEGIN
UPDATE SYSCurrency SET OneUnitBaseEquals = (
SELECT 1/OneEuroEquals
FROM SYSCurrency
WHERE ThisIsBaseCurrency = 1)
WHERE ThisIsEuroCurrency = 1
END
END
END
This now fires whenever any row is amended, but only updates the Euro currency exchange rate if it is the base currency and is not the Euro currency.
Hopefully this little fix will help anyone who also needs to do exchange rate conversions using the SQL tables.
