CODEWITHSUNDEEP
sqlfirebird
SteveS
SteveS

Reputation: 407

Firebird increment a field value in place on update

I'm using Firebird 2.5.9. I have a table of information on a set of hardware impact devices that includes a running counter of the # of times the device has impacted. Each time a device is "fired", the hardware will impact 1 or more times; upon completion of the firing event, that device's row is updated with the timestamp and a result code, and I need to increment the running counter column with the number of impacts for that fire event.

I can do this as a separate query to get the field's current value, increment it and use that new value in the update statement, but that seems like a lot of extra overhead. This sort of scenario can't be that uncommon, so I assume that there's some straightforward way to do this within an update statement, but I don't know what it is. I also realize that I could do this as a stored procedure, but for now I want to just do it in the update statement if possible.

Upvotes: 0

Views: 482

Answers (2)

SteveS
SteveS

Reputation: 407

I've done this for now by expanding the existing before-insert trigger to a before-insert-or-update trigger:

CREATE TRIGGER TBIU_RPRS1 FOR RPRS ACTIVE BEFORE INSERT OR UPDATE
AS BEGIN
  IF (INSERTING AND NEW.ID IS NULL) THEN NEW.ID = NEXT VALUE FOR SEQ_GLOBAL;
  IF (UPDATING) THEN NEW.STRIKES = OLD.STRIKES + NEW.STRIKES;
END;

Upvotes: 1

user13964273
user13964273

Reputation: 1221

Running counters, sums, etc used to be called "stored aggregates". They are usually maintained by triggers on events tables. But before using them make sure that a simple view with plain count() is not fast enough for you.

Upvotes: 0

Related Questions