Hi,
I have a table referancing services of a company :
CREATE TABLE Services ( id INTEGER NOT NULL PRIMARY KEY, nom VARCHAR(30) NOT NULL -- name of the service
);
each of them can have many workers and many boss
but at least 1 boss
So i have a table referancing workers and boss :
CREATE TABLE EmployesDsServices ( service INTEGER REFERENCES Services (id) ON DELETE CASCADE
DEFERRABLEINITIALLY DEFERRED, employe INTEGER REFERENCES Employes (id) ON DELETE CASCADE DEFERRABLE
INITIALLYDEFERRED, niveau CHAR(1), -- R -> boss, S -> worker CONSTRAINT niveau_valide CHECK (NIVEAU IN
('R','S')), PRIMARY KEY (service, employe)
);
to check that at any time a service always have at least one boss,
i would like :
CREATE ASSERTION chaque_service_a_au_moins_un_responsable CHECK (NOT EXISTS (SELECT id FROM Services LEFT OUTER
JOIN (SELECT * FROM employesdsservices WHERE niveau='R') AS resp ON id=service GROUP BY id HAVING
COUNT(niveau)<1));
the problem is that this is not valid in postgreSQL,
so i tryed to use a trigger, but it seems that it is not possible to
make a trigger deferrable (as the 2 tables are mutualy dependent).
i would like something i can use like this :
begin;
insert into services ...
insert into employesdsservices ...
end;
is there a way to do this, a check after each insert, update, delete in
both tables?
i know my constraint is anyway very slow, but this is for a scolar
project about theory and performance realy dont matters.
thank you for help
Edouard Boucher
