On Thu, Mar 12, 2009 at 15:00, Maya Opperman <maya@...> wrote:
> I presume I need to have some kind of an event logging table and insert
> the relevant information in there. In which case, I don't even need
> events at all anyway?? (OK, maybe just one event on my EventLog table
> every time a record is inserted to save me polling it periodically)
>
> I'm getting the feeling I might be missing the whole point of Firebird
> events?

this is, how I use events to get notification of changed records
(though the original idea for this design was mine, it was Jason
Wharton from www.ibobjects.com who kindly provided me with a first
implementation -- the following is slightly customized from his
design):

1.) Create table to hold notification info:

CREATE TABLE IBO$DMLCACHE (
DMLID INTEGER NOT NULL,
DMLACTION CHAR(1) NOT NULL,
KEYFIELDS VARCHAR(128) NOT NULL,
KEYVALUE1 VARCHAR(128) NOT NULL,
KEYVALUE2 VARCHAR(128),
KEYVALUE3 VARCHAR(128),
KEYVALUE4 VARCHAR(128),
DMLUSER VARCHAR(32) NOT NULL,
DMLTIMESTAMP TIMESTAMP NOT NULL,
DMLCONNECTION INTEGER NOT NULL,
DMLTRANSACTION INTEGER NOT NULL )^

ALTER TABLE IBO$DMLCACHE
ADD CONSTRAINT PK_IBO$DMLCACHE
PRIMARY KEY ( DMLID )^

CREATE TRIGGER BI_IBO$DMLCACHE FOR IBO$DMLCACHE
BEFORE INSERT
AS
DECLARE VARIABLE var_CacheCounter INTEGER;
DECLARE VARIABLE var_LastDMLID INTEGER;
BEGIN
/* make sure that fields to insert are not null */
IF (NEW.DMLID IS NULL) THEN BEGIN
NEW.DMLID = GEN_ID( GEN_IBO$DMLCACHE, 1 );
END
IF (NEW.DMLUSER IS NULL) THEN BEGIN
NEW.DMLUSER = USER;
END
IF (NEW.DMLTIMESTAMP IS NULL) THEN BEGIN
NEW.DMLTIMESTAMP = 'NOW';
END
IF (NEW.DMLCONNECTION IS NULL) THEN BEGIN
NEW.DMLCONNECTION = CURRENT_CONNECTION;
END
IF (NEW.DMLTRANSACTION IS NULL) THEN BEGIN
NEW.DMLTRANSACTION = CURRENT_TRANSACTION;
END

/* simple counter in TBL_DBCTRL that gets incremented
every time a new record gets inserted. If the counter doesn't
exists then insert it */
SELECT INTVALUE
FROM TBL_DBCTRL
WHERE FLDNAME = 'CACHECOUNTER'
INTO :var_CacheCounter;
IF (var_CacheCounter IS NULL) THEN BEGIN
var_CacheCounter = 0;
INSERT INTO TBL_DBCTRL (FLDNAME, INTVALUE)
VALUES ('CACHECOUNTER', :var_CacheCounter);
END

IF (var_CacheCounter >= 300) THEN BEGIN
/* to save processing time, do this only every 300 inserted
DMLCache records: get last DMLId, then delete all records
except the newest 1000 and, finally, reset the CacheCounter
in TBL_DBCTRL back to 0 */
SELECT MAX(DMLID) FROM IBO$DMLCACHE INTO :var_LastDMLID;
DELETE FROM IBO$DMLCACHE
WHERE (DMLID < :var_LastDMLID - 1000);
UPDATE TBL_DBCTRL
SET INTVALUE = 0
WHERE (FLDNAME = 'CACHECOUNTER');
END ELSE BEGIN
/* no delete, just increment CacheCounter */
UPDATE TBL_DBCTRL
SET INTVALUE = :var_CacheCounter + 1
WHERE (FLDNAME = 'CACHECOUNTER');
END

WHEN SQLCODE -901 DO BEGIN
/* ignore Deadlocks */
END
END^

CREATE TRIGGER AI_IBO$DMLCACHE FOR IBO$DMLCACHE
AFTER INSERT
AS
BEGIN
POST_EVENT 'IBO$DMLCACHE';
END^


2.) Add a trigger to table you want to get your notification from:

CREATE TRIGGER CLIRES_DMLC FOR CLIRES
AFTER INSERT OR UPDATE OR DELETE
AS
BEGIN
IF (INSERTING) THEN BEGIN
INSERT INTO IBO$DMLCACHE (DMLACTION, KEYFIELDS, KEYVALUE1)
VALUES ('I', 'CR_ID_CLIRES', NEW.CR_ID_CLIRES);
END ELSE
IF (UPDATING) THEN BEGIN
INSERT INTO IBO$DMLCACHE (DMLACTION, KEYFIELDS, KEYVALUE1)
VALUES ('U', 'CR_ID_CLIRES', NEW.CR_ID_CLIRES);
END ELSE
IF (DELETING) THEN BEGIN
INSERT INTO IBO$DMLCACHE (DMLACTION, KEYFIELDS, KEYVALUE1)
VALUES ('D', 'CR_ID_CLIRES', OLD.CR_ID_CLIRES);
END
END^

3.) Setup some event handling in your application:

When you initialize the event handling to listen for 'IBO$DMLCACHE',
then first fetch the latest DMLCacheId:
var_LastProcessedDMLID = SELECT MAX(DMLID) FROM IBO$DMLCACHE

In the event handler you can then get all DMLCache entries you haven't
processed yet:

SELECT MAX(DML.DMLID), DML.DMLACTION, DML.KEYFIELDS,
DML.KEYVALUE1, DML.KEYVALUE2, DML.KEYVALUE3, DML.KEYVALUE4,
DML.DMLUSER, DML.DMLTIMESTAMP
FROM IBO$DMLCACHE DML
WHERE (DML.DMLID > :var_LastProcessedDMLID )
AND (DML.DMLCONNECTION <> CURRENT_CONNECTION)
GROUP BY DML.DMLACTION, DML.KEYFIELDS,
DML.KEYVALUE1, DML.KEYVALUE2, DML.KEYVALUE3, DML.KEYVALUE4,
DML.DMLUSER, DML.DMLTIMESTAMP

You can then iterate over this set of records and e.g. refresh your
local datasets if the KeyFields match.

HTH,
Markus