Thread: FOR EACH STATEMENT trigger ?
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_FINIS 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 *************************
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édéric 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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >