[SQL] FOR EACH STATEMENT trigger ?

[Frédéric BROUARD]

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  DATENOT NULL,
 PDD_ENDDATE,
 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

Re: [SQL] check constraint bug?

[Lew]

Scott Marlowe wrote:

Tarlika Elisabeth Schmitz wrote:

I specified:

ALTER TABLE h ADD CONSTRAINT val_h_stats
CHECK (NOT (sex = 'f') AND (stats IS NOT NULL));

which was translated to:

ALTER TABLE h ADD CONSTRAINT val_h_stats
CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL);



You need another level of parens:

CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL)));


Because NOT has higher precedence than AND.



Note that equals (=), IS and NOTNULL have higher precedence than NOT.

So the CHECK expression Scott indicated is equivalent to the parenthesis-minimal

 CHECK ( NOT ( sex = 'f' AND stats IS NOT NULL ) )

or

 CHECK ( sex != 'f' OR stats IS NULL )

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql