DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=fktweg5sz3HsZ50Fs/A3/CoMWFQ+o0xHgeYiXgAQTT6HjDqMIrUyJtYazrCCzhm4Nx
	1jNYdKi7keSWxkhHHpmKZASJBRKu6tj9xinS7lW3ohCEtQgM9fpruyaN5MuoHTBFdrRX
	9S75SLsb5ECFGknTB01F/uPRXhmdutn3zh8Yk=
MIME-Version: 1.0
In-Reply-To: <4DC42C5B.1000007@club-internet.fr>
References: <4DC42C5B.1000007@club-internet.fr>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 9 May 2011 12:18:09 +0200
Message-ID: <BANLkTim++uOvPVW-jAR98rNavxOqd08EvA@mail.gmail.com>
Subject: Re: FOR EACH STATEMENT trigger ?
To: =?UTF-8?B?RnLDqWTDqXJpYyBCUk9VQVJE?= <brouardf@club-internet.fr>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello

it isn't bug. PostgreSQL doesn't support NEW and OLD tables like MSSQL
does for statement triggers.

Regards

Pavel Stehule

2011/5/6 Fr=C3=A9d=C3=A9ric BROUARD <brouardf@club-internet.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.
>
> Let me give you a real life example.
>
> Suppose we have the above table :
>
> CREATE TABLE T_PRODUIT_DISPO_PDD
> (PRD_ID =C2=A0 =C2=A0 =C2=A0 =C2=A0 INT =C2=A0 =C2=A0 =C2=A0 =C2=A0 NOT N=
ULL,
> =C2=A0PDD_BEGIN =C2=A0 =C2=A0 =C2=A0DATE =C2=A0 =C2=A0 =C2=A0 =C2=A0NOT N=
ULL,
> =C2=A0PDD_END =C2=A0 =C2=A0 =C2=A0 =C2=A0DATE,
> =C2=A0PDD_QUANTITY =C2=A0 FLOAT =C2=A0 =C2=A0 =C2=A0 NOT NULL);
>
> We want to never have more thant one PDD_END =3D NULL for the same PRD_ID=
.
>
> The assertion we can do is :
>
> ALTER TABLE T_PRODUIT_DISPO_PDD
> =C2=A0 ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL
> =C2=A0 =C2=A0 =C2=A0CHECK (NOT EXISTS(SELECT 0
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0FROM =C2=A0 T_PRODUIT_DISPO_PDD
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0WHERE =C2=A0PDD_FIN IS NULL
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0GROUP =C2=A0BY PRD_ID
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0HAVING 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
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM =C2=A0 T_PRODUIT_DISPO_PDD
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE =C2=A0PRD_ID IN(SELECT NE=
W.PRD_ID
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM =C2=A0 NEW) AS T
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND =C2=A0PDD_END IS NUL=
L
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GROUP =C2=A0BY PRD_ID
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 HAVING COUNT(*) > 1);
> IF ( n_rows IS NOT NULL )
> =C2=A0 THEN RAISE EXCEPTION 'Violation de la contrainte d''unicit=C3=A9 s=
ur le couple
> de colonne PRD_ID + PDD_FIN';
> =C2=A0 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
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM =C2=A0 T_PRODUIT_DISPO_PDD
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE =C2=A0PRD_ID =3D NEW.PRD_=
ID
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND =C2=A0PDD_FIN IS NUL=
L
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GROUP =C2=A0BY PRD_ID
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 HAVING COUNT(*) > 1);
> IF ( n_rows IS NOT NULL )
> =C2=A0 THEN RAISE EXCEPTION 'Violation de la contrainte d''unicit=C3=A9 s=
ur le couple
> de colonne PRD_ID + PDD_FIN';
> =C2=A0 ROLLBACK TRANSACTION;
> END IF;
> RETURN NULL;
> END
> $code$ LANGUAGE 'plpgsql' VOLATILE
>
> CREATE TRIGGER E_IU_PRD
> =C2=A0 AFTER INSERT OR UPDATE
> =C2=A0 ON T_PRODUIT_DISPO_PDD
> =C2=A0 FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN();
>
>
> But it is absolutly not that I Want !!!!
>
> Thanks
>
>
> --
> Fr=C3=A9d=C3=A9ric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11=
 86 40 66
> Le site sur le langage SQL et les SGBDR =C2=A0: =C2=A0http://sqlpro.devel=
oppez.com
> Enseignant Arts & M=C3=A9tiers PACA, ISEN Toulon et CESI/EXIA Aix en Prov=
ence
> Audit, conseil, expertise, formation, mod=C3=A9lisation, tuning, optimisa=
tion
> *********************** http://www.sqlspot.com *************************
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>