Simple Delphi trigger example

[classic12]

Hi guys
New to PostgreSQL
Trialing PgDAC for Delphi.

In a cross platform app I need to get an alert if a table has an inserted row.

Eg
Database - temp
Table name = customers

Oninsert row trigger

Memo1.lines.add(‘ new row inserted ‘ + insert ID )

Anyone have a sample code for the trigger and in Delphi for the alert ?

Cheers

[oleg0k]

Hello,
PgDAC provides the TPgAlerter component, which is intended for "getting feedback" from the database system to the application.
See the PgDAC documentation for more information about TPgAlerter.
You can find an example of using TPgAlerter in the PgDacDemo project, which is located in <Your Documents>\Devart\PgDAC for RAD Studio 10.3\Demos

wbr, Oleg
Devart Team

[classic12]

Hi,

I have purchased the Delphi component. New to this. I see the examples and have connected my database and created the sample tables.

How would I simply add to a memo when the emp table has an update or an insert.

Cheers

[classic12]

I have added the sample SLQ tables.

In my app I add a pgAlerter.
In the Events I have PGDac ( I can't find any details on which events are available )
Active to true.
On the pgAlerterEvent I have:

Form6.memoLog1.Lines.Add('Event: ' + EventName + '; PID: ' + IntToStr(PID) + ' message: ' + EventMessage );

When I update / insert on the Empty Table nothing happens.

How do I implement this please ( AT IDIOT LEVEL PLEASE )

[oleg0k]

Hello,
Is Alert in the demo project working in your environment? What difficulties are you having?
Please create an example (with comments) demonstrating the issue.

wbr, Oleg
Devart Team

[classic12]

The app Just has the code above and a grid linked to the emp table.
The alert does not Happen when I update or insert into the emp table.

[oleg0k]

Hello,
The PostgreSQL server should send the necessary message to your application.
In your example, a trigger should be created with the CREATE TRIGGER command, that should execute the command
NOTIFY EventName, 'Insert/Update happened !'
when an After insert/After update event occurs in your table.

wbr, Oleg
Devart Team

[classic12]

Ok.

What is the syntax for the trigger please.

Also what is the list of events I can listen for ? ( can't find these in the documents ).

Cheers

[oleg0k]

Hello,
Your question is related to PostgreSQL features rather than our components. Please refer to the PostgreSQL documentation for information: https://www.postgresql.org/docs/12/sql- ... igger.html

wbr, Oleg
Devart Team

[classic12]

I also asked :

Also what is the list of events I can listen for ? ( can't find these in the documents ).

I bought your component with the hope of getting some support and help through your supplied examples. You show an an example of a trigger event ( the main reason for the purchase ) but the code for the trigger is not in there.

Please update the sample to include the code required to function correctly.

[oleg0k]

Hello,
TPgAlerter can listen to an arbitrary list of events, which can be defined in the Events property: https://www.devart.com/pgdac/docs/devar ... events.htm
To initialize a database event in the application, you should execute the command NOTIFY with the event name in the necessary trigger, which the alerter listens to
(https://www.postgresql.org/docs/12/sql-notify.html).
The demo project doesn't contain a trigger since events are initialized directly in the sample application.
In your example, a trigger may look like this:

Code: Select all

CREATE TRIGGER tblexample_after_ins
    AFTER INSERT
    ON public.tblexample
    FOR EACH ROW
    EXECUTE PROCEDURE public.notify();

CREATE FUNCTION public.notify()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$BEGIN
	NOTIFY myPGEventName, 'myPGEventName!';
    RETURN new;
END;

wbr, Oleg
Devart Team