pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFOR EACH STATEMENT trigger ?
4+ messages / 3 participants
[nested] [flat]
* FOR EACH STATEMENT trigger ?
@ 2011-05-06 17:14 Frédéric BROUARD <brouardf@club-internet.fr>
2011-05-09 10:18 ` Re: FOR EACH STATEMENT trigger ? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frédéric BROUARD @ 2011-05-06 17:14 UTC (permalink / raw)
To: pgsql-sql
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 *************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FOR EACH STATEMENT trigger ?
2011-05-06 17:14 FOR EACH STATEMENT trigger ? Frédéric BROUARD <brouardf@club-internet.fr>
@ 2011-05-09 10:18 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2011-05-09 10:18 UTC (permalink / raw)
To: Frédéric BROUARD <brouardf@club-internet.fr>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* FOR EACH STATEMENT trigger ?
@ 2011-05-06 21:57 F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-05-06 22:11 ` Re: FOR EACH STATEMENT trigger ? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: F. BROUARD / SQLpro @ 2011-05-06 21:57 UTC (permalink / raw)
To: pgsql-sql
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 *************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FOR EACH STATEMENT trigger ?
2011-05-06 21:57 FOR EACH STATEMENT trigger ? F. BROUARD / SQLpro <sqlpro@club-internet.fr>
@ 2011-05-06 22:11 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2011-05-06 22:11 UTC (permalink / raw)
To: F. BROUARD / SQLpro <sqlpro@club-internet.fr>; +Cc: pgsql-sql
Hello,
2011/5/6 F. BROUARD / SQLpro <sqlpro@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.
>
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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-05-09 10:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-06 17:14 FOR EACH STATEMENT trigger ? Frédéric BROUARD <brouardf@club-internet.fr>
2011-05-09 10:18 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-05-06 21:57 FOR EACH STATEMENT trigger ? F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-05-06 22:11 ` Pavel Stehule <pavel.stehule@gmail.com>
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