Re: FOR EACH STATEMENT trigger ?

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: "F(dot) BROUARD / SQLpro" <sqlpro(at)club-internet(dot)fr>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: FOR EACH STATEMENT trigger ?
Date: 2011-05-06 22:11:24
Message-ID: BANLkTi=Tk=xST1M8OqaGf_+jciNB_adGmA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

2011/5/6 F. BROUARD / SQLpro <sqlpro(at)club-internet(dot)fr>:
>
> Hi there
>
> I am trying to get an example of SET BASED trigger logic with FOR EACH
> STATEMENT, but I cannot find any example involving the pseudo table NEW
> (or OLD) in the trigger function SQL statement.
>

PostgreSQL doesn't support NEW or OLD tables in statement triggers.
You should to use ROW triggers.

Regards

Pavel Stehule

> Let me give you a real life example.
>
> Suppose we have the above table :
>
> CREATE TABLE T_PRODUIT_DISPO_PDD
> (PRD_ID         INT         NOT NULL,
>  PDD_BEGIN      DATE        NOT NULL,
>  PDD_END        DATE,
>  PDD_QUANTITY   FLOAT       NOT NULL);
>
> We want to never have more thant one PDD_END = NULL for the same PRD_ID.
>
> The assertion we can do is :
>
> ALTER TABLE T_PRODUIT_DISPO_PDD
>   ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL
>      CHECK (NOT EXISTS(SELECT 0
>                        FROM   T_PRODUIT_DISPO_PDD
>                        WHERE  PDD_FIN IS NULL
>                        GROUP  BY PRD_ID
>                        HAVING COUNT(*) > 1))
>
> Which is not supported by PG
>
> So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a
> FOR EACH ROW.
>
> Here is the code I try :
>
> CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS
> $code$
> DECLARE n_rows integer;
> BEGIN
> SELECT COUNT(*) INTO n_rows
> WHERE EXISTS(SELECT 0
>             FROM   T_PRODUIT_DISPO_PDD
>             WHERE  PRD_ID IN(SELECT NEW.PRD_ID
>                              FROM   NEW) AS T
>               AND  PDD_END IS NULL
>             GROUP  BY PRD_ID
>             HAVING COUNT(*) > 1);
> IF ( n_rows IS NOT NULL )
>   THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le
> couple de colonne PRD_ID + PDD_FIN';
>   ROLLBACK TRANSACTION;
> END IF;
> RETURN NULL;
> END
> $code$ LANGUAGE 'plpgsql' VOLATILE
>
> Which produce an error !
>
> Of course I can do that with a FOR EACH STATEMENT like this one :
>
> CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS
> $code$
> DECLARE n_rows integer;
> BEGIN
> SELECT 1 INTO n_rows
> WHERE EXISTS(SELECT 0
>             FROM   T_PRODUIT_DISPO_PDD
>             WHERE  PRD_ID = NEW.PRD_ID
>               AND  PDD_FIN IS NULL
>             GROUP  BY PRD_ID
>             HAVING COUNT(*) > 1);
> IF ( n_rows IS NOT NULL )
>   THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le
> couple de colonne PRD_ID + PDD_FIN';
>   ROLLBACK TRANSACTION;
> END IF;
> RETURN NULL;
> END
> $code$ LANGUAGE 'plpgsql' VOLATILE
>
> CREATE TRIGGER E_IU_PRD
>   AFTER INSERT OR UPDATE
>   ON T_PRODUIT_DISPO_PDD
>   FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN();
>
>
> But it is absolutly not that I Want !!!!
>
> Thanks
>
>
> --
> Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
> Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
> Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> *********************** http://www.sqlspot.com *************************
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Tarlika Elisabeth Schmitz 2011-05-07 09:15:47 Re: data import via COPY, Rules + Triggers
Previous Message F. BROUARD / SQLpro 2011-05-06 21:57:54 FOR EACH STATEMENT trigger ?