How to tell which event was fired in Trigger function

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How to tell which event was fired in Trigger function

Igal @ Lucee.org

Hi,

I am writing a trigger function that is used after DELETE, INSERT, and UPDATE, like so:

    CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ...

How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE? 

The table has a `NOT NULL id` column, so I am thinking that maybe a DELETE will have a NULL value in NEW.id?  Will that work?  Is there a better way?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

Reply | Threaded
Open this post in threaded view
|

Re: How to tell which event was fired in Trigger function

Adrian Klaver-4
On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:

> Hi,
>
> I am writing a trigger function that is used after DELETE, INSERT, and
> UPDATE, like so:
>
>      CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ...
>
> How can I tell inside the trigger function if the event was DELETE or
> INSERT/UPDATE?
>
> The table has a `NOT NULL id` column, so I am thinking that maybe a
> DELETE will have a NULL value in NEW.id?  Will that work?  Is there a
> better way?

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

>
> Thanks,
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org <http://lucee.org/>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to tell which event was fired in Trigger function

Igal @ Lucee.org
On 7/11/2018 10:38 AM, Adrian Klaver wrote:
> On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
>> How can I tell inside the trigger function if the event was DELETE or
>> INSERT/UPDATE?
>
> https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

This looks like it have all of the information that I need.  For some
reason search engines bring up many other pages but not that one.

Thanks Adrian!

Igal

Reply | Threaded
Open this post in threaded view
|

Re: How to tell which event was fired in Trigger function

David G Johnston
On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org <[hidden email]> wrote:
On 7/11/2018 10:38 AM, Adrian Klaver wrote:
On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE?

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

This looks like it have all of the information that I need.  For some reason search engines bring up many other pages but not that one.

​For these situations I recommend bookmarking and navigating to the Table of Contents [1] for the documentation and looking for relevant chapter titles - in this case the "V. 38 - Triggers" one is right there on the main page.


On a related note, maybe the pl/pgsql link from the Triggers chapter should link directly to the Triggers section under pl/pgsql instead of the top of the chapter...

David J.

Reply | Threaded
Open this post in threaded view
|

Re: How to tell which event was fired in Trigger function

Igal @ Lucee.org
On 7/11/2018 11:02 AM, David G. Johnston wrote:
On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org <[hidden email]> wrote:
On 7/11/2018 10:38 AM, Adrian Klaver wrote:
On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE?

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

This looks like it have all of the information that I need.  For some reason search engines bring up many other pages but not that one.

​For these situations I recommend bookmarking and navigating to the Table of Contents [1] for the documentation and looking for relevant chapter titles - in this case the "V. 38 - Triggers" one is right there on the main page.


On a related note, maybe the pl/pgsql link from the Triggers chapter should link directly to the Triggers section under pl/pgsql instead of the top of the chapter...

+1

Should link to ¶42.9 - https://www.postgresql.org/docs/10/static/plpgsql-trigger.html