Simple Delphi trigger example

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
classic12
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Simple Delphi trigger example

Post by classic12 » Fri 08 May 2020 07:50

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

Re: Simple Delphi trigger example

Post by oleg0k » Fri 08 May 2020 12:43

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
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Re: Simple Delphi trigger example

Post by classic12 » Fri 15 May 2020 14:10

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
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Re: Simple Delphi trigger example

Post by classic12 » Sun 17 May 2020 10:43

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

Re: Simple Delphi trigger example

Post by oleg0k » Tue 19 May 2020 11:26

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
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Re: Simple Delphi trigger example

Post by classic12 » Tue 19 May 2020 13:28

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

Re: Simple Delphi trigger example

Post by oleg0k » Wed 20 May 2020 14:30

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
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Re: Simple Delphi trigger example

Post by classic12 » Wed 20 May 2020 15:32

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

Re: Simple Delphi trigger example

Post by oleg0k » Thu 21 May 2020 08:14

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
Posts: 21
Joined: Mon 20 Apr 2009 09:48

Re: Simple Delphi trigger example

Post by classic12 » Thu 21 May 2020 08:26

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
Devart Team
Posts: 65
Joined: Wed 11 Mar 2020 08:28

Re: Simple Delphi trigger example

Post by oleg0k » Thu 21 May 2020 13:03

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

Post Reply