Using Triggers in Firebird invoicing app to calculate time-based charges

[frofa]

Hello:

I am trying to use an SQL TRIGGER to calculate time-based charges on an invoicing app I have made using Base (Firebird Embedded). Generally, for each time-slip, charges are based on the time elapsed (in minutes) multipled by the charge rate (per hour). This code works fine.

CREATE TRIGGER timeslip_chrg FOR "timeslip"
ACTIVE BEFORE INSERT OR UPDATE POSITION 12
AS
BEGIN
new."charge" = ((new."time_end" - new."time_start" ) * new."rate") / (60*60);
END

However, I sometimes need to calculate charges differently for NON-time-based charges such as for items already paid for, requiring reimbursement. Then it’s just the “rate” for the item. So, this code would be fine.

CREATE TRIGGER timeslip_chrg FOR "timeslip"
ACTIVE BEFORE INSERT OR UPDATE POSITION 12
AS
BEGIN
new."charge" = new."rate";
END

Which one of these 2 possibilities is indicated by an INTEGER column “ty” which is set to ‘1’ for time-based charges and ‘2’ for rate-based charges (as in second trigger above).

My question: is it possible to contruct a single TRIGGER that can do both, perhaps using the FB SQL CASE … WHEN … ELSE… syntax.

I have tried…

ALTER TRIGGER timeslip_chrg
ACTIVE BEFORE INSERT OR UPDATE POSITION 12
AS
BEGIN
CASE new."ty"
WHEN '2'
THEN new."charge" = new."rate"
ELSE new."charge" = ((new."time_end" - new."time_start" ) * new."rate") / (60*60);
END

… but there is an error for *CASE. Any suggestions on how to get this to work?

[Ratslinger]

Hello,

My understanding is that CASE is only allowed in a Select statement.

This worked in my test:

CREATE TRIGGER timeslip_chrg FOR "timeslip"
ACTIVE BEFORE INSERT OR UPDATE POSITION 12
AS
BEGIN
  if (new."ty" = 1)
    then new."charge" = ((new."time_end" - new."time_start" ) * new."rate") / (60*60);
  if (new."ty" = 2)
    then new."charge" = new."rate";
END

Edit:

Another variation, for the if statements:

CREATE TRIGGER timeslip_chrg FOR "timeslip"
ACTIVE BEFORE INSERT OR UPDATE POSITION 12
AS
BEGIN
  if (new."ty" = 1)
    then new."charge" = ((new."time_end" - new."time_start" ) * new."rate") / (60*60);
  if (new."ty" <> 1)
    then new."charge" = new."rate";
END

[frofa]

@Ratslinger: that works fine. Thank you very much for your help.