Message-ID: <4DC42C5B.1000007@club-internet.fr>
Date: Fri, 06 May 2011 19:14:03 +0200
From: =?ISO-8859-1?Q?Fr=E9d=E9ric_BROUARD?= <brouardf@club-internet.fr>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: FOR EACH STATEMENT trigger ?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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.

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 *************************