From: Frédéric BROUARD <brouardf@club-internet.fr>
To: pgsql-sql@postgresql.org
Subject: FOR EACH STATEMENT trigger ?
Date: Fri, 06 May 2011 19:14:03 +0200
Message-ID: <4DC42C5B.1000007@club-internet.fr> (raw)
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 *************************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: brouardf@club-internet.fr
Subject: Re: FOR EACH STATEMENT trigger ?
In-Reply-To: <4DC42C5B.1000007@club-internet.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox