How to make an event after insert on Firedac Sqlite database connection?

Question

I would like to have a hook on my database connection. Every time there's an update on any table in the database, I'd like to use a function in my code(preferably with parameters from the executed query if that's possible).

I'm using the FireDac components; TFDConnection, TFDPhysSQLiteDriverLink, TFDQuery and TFDEventAlerter.

Code at init:

  FDEventAlerter1.Names.Clear;
  FDEventAlerter1.Names.Add('intEv');
  FDEventAlerter1.Options.Synchronize := true;
  FDEventAlerter1.Options.Kind := 'Events';
  FDEventAlerter1.OnAlert := FDEventAlerter1Alert;
  FDEventAlerter1.Active := true;

Code at OnAlert

procedure TDataModule1.FDEventAlerter1Alert(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
begin
  ShowMessage('test');
end;

Code from CreateTable query:

CREATE TABLE IF NOT EXISTS int(
   ValuesInt INTEGER NOT NULL,
   ValuesIntTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TRIGGER IF EXISTS intEv;
CREATE TRIGGER IF NOT EXISTS intEv 
AFTER INSERT ON int
BEGIN
  SELECT POST_EVENT('trigger');
END;

Code at insert query which gets executed every 10 sec:

INSERT INTO int(valuesint) VALUES (:int);

When I run this, the database gets filled with integers, but no event is triggered.

See Tracing and Monitoring (FireDAC) and the TFDMoniCustomClientLink component's OnOutput event. — Brian
In addition to @Brian's link, you might take a look at docwiki.embarcadero.com/RADStudio/XE8/en/…. — MartynA

M. Boot M. Boot · Accepted Answer · 2019-08-08T13:36:35

the TSQLiteDatabase.onUpdate (chapter 9.1) gave me what I needed.

sqlDb: TSQLiteDatabase;

sqlDb := TSQLiteDatabase(FDConnection1.CliObj);
sqlDb.OnUpdate := DoUpdate;

procedure TDataModule1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase, ATable: String; ARowid: int64);
begin
  //http://www.sqlite.org/c3ref/c_alter_table.html
  if AOper = {SQLITE_INSERT}18 then
    sleep(1);
end;

How to make an event after insert on Firedac Sqlite database connection?

1 Answers