Ask Your Question
0

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

asked 2020-09-01 03:35:00 +0100

frofa gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-09-01 04:07:19 +0100

Ratslinger gravatar image

updated 2020-09-01 04:15:46 +0100

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
edit flag offensive delete link more

Comments

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

frofa gravatar imagefrofa ( 2020-09-01 05:02:58 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-01 03:35:00 +0100

Seen: 50 times

Last updated: Sep 01 '20