DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:x-enigmail-version:content-type:content-transfer-encoding;
	b=ZsyvAAY2kWJIGbR4B0BlnMIevqaLfx7714OfGGpOxK53jrvRjffJUZghpiquJzLlTPAmXa/AVYMM6ikvS/yNkryHx7Pd02tuBRK3M7kX9HjTuHi6a1baibee9AjMJZRcbTIJ/Ai7Y0XuYDeRmN3NgckchkXLNg5HKSzWJAv6rgY=
Message-ID: <42787A43.7030406@gmail.com>
Date: Wed, 04 May 2005 09:31:15 +0200
From: Sonic <sonicaaaa@gmail.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: CASCADE and TRIGGER - Some weird problem
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hi, have a little problem with a trigger and the ON DELETE CASCADE
statement.

i'm working on a db that represents Users and Messages. A message can be
owned by more than a user.  if i delete a user all his related objects
are deleted too (ON DELETE CASCADE), but if a message that this user
owns is also owned by another user, it has not to be deleted.

i just put ON DELETE CASCADE statement on foreign keys and thos seem to
work.

then i wrote a trigger to check if each message the user owns is owned
by someone else. if it's not delete it!
- -----------------------------------------------------------
- -----this is the code:

CREATE TABLE OWNER(
	_LOGIN		TEXT,
	PRIMARY KEY(_LOGIN) );

CREATE TABLE MESSAGE(
	_MESSAGE_ID		TEXT,
	PRIMARY KEY(_MESSAGE_ID)
	);

CREATE TABLE ACCOUNT(
	_INDIRIZZO		TEXT UNIQUE,
	_LOGIN		TEXT,
	PRIMARY KEY(_INDIRIZZO,_LOGIN),
	FOREIGN KEY(_LOGIN)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE);

CREATE TABLE ACCOUNT_MESSAGE(
	_MSG		TEXT,
	_INDIRIZZO		TEXT,
	PRIMARY KEY(_MSG,_INDIRIZZO),
	FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID),
	FOREIGN KEY(_INDIRIZZO)REFERENCES ACCOUNT(_INDIRIZZO) ON DELETE CASCADE);

CREATE TABLE FOLDER(
	_PATH		TEXT,
	_OWNER		TEXT,
	PRIMARY KEY(_PATH),
	FOREIGN KEY(_OWNER)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE);

CREATE TABLE MSG_IN_FOLDER(
	_MSG		TEXT,
	_FOLDER		TEXT,
	PRIMARY KEY(_MSG,_FOLDER),
	FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID),
	FOREIGN KEY(_FOLDER)REFERENCES FOLDER(_PATH) ON DELETE CASCADE);

CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$
	BEGIN
		DELETE FROM MESSAGE WHERE _message_id IN (	SELECT _MSG 																			
						FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
						WHERE _MSG = OLD._MSG
						GROUP BY _MSG
						HAVING count(*)=1
					);
		RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG;
		RETURN NULL;
	END;
$check_MESSAGE$ LANGUAGE plpgsql;

CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER
	FOR EACH ROW EXECUTE PROCEDURE check_message();
- -------------------------------------------------

- --and these are some values:


delete from OWNER;
delete from ACCOUNT;
delete from MESSAGE;
delete from ACCOUNT_MESSAGE;
delete from FOLDER;
delete from MSG_IN_FOLDER;

insert into OWNER (_login) values ('anna');
insert into OWNER (_login) values ('paolo');
insert into ACCOUNT values ('anna@gmail.com', 'anna');
insert into ACCOUNT values ('paolo@gmail.com', 'paolo');
insert into MESSAGE (_message_id) values ('1');
insert into MESSAGE (_message_id) values ('2');
insert into ACCOUNT_MESSAGE values ('1', 'anna@gmail.com');
insert into ACCOUNT_MESSAGE values ('1', 'paolo@gmail.com');
insert into ACCOUNT_MESSAGE values ('2', 'anna@gmail.com');
insert into FOLDER (_path, _OWNER) values ('c', 'anna');
insert into MSG_IN_FOLDER values ('1', 'c');
insert into MSG_IN_FOLDER values ('2', 'c');

select * from MESSAGE;

- ----------


as you see there are 2 messages. message 1 is owned both by 'anna' and
'paolo'. message 2 is owned just by 'anna'.

now what i want is that if i delete user 'anna' just message 2 is deleted.

i guess i've done that with my trigger:


...
CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$
	BEGIN
		DELETE FROM MESSAGE WHERE _message_id IN (	SELECT _MSG 																			
						FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
						WHERE _MSG = OLD._MSG
						GROUP BY _MSG
						HAVING count(*)=1
					);
		RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG;
		RETURN NULL;
	END;
$check_MESSAGE$ LANGUAGE plpgsql;

CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER
	FOR EACH ROW EXECUTE PROCEDURE check_message();
...


i'm also sure that values it process are right beacuse of that RAISE
NOTICE 'Value of OLD._MSG %', OLD._MSG;

but something seems to go wrong.
all 'anna' stuff is deleted but not her message 2 !


DELETE FROM owner WHERE _login = 'anna';
SELECT * FROM message;


can someone help me to understand why this is not done?

i don't receive any error message from postgres...


thanks

paolo from italy

sonicaaaa@gmail.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (MingW32)

iD8DBQFCeHpD8gTT7JZTWqIRAvc6AJ9yqXq4EOP+JZ4NJ+pekiwuqko0XACeMz/8
DBZdQevWM1emBodYH5QP0G4=
=V6VW
-----END PGP SIGNATURE-----