Hi,
The most general problem that may be a design problem (and I'm not asking to do
my homework even if well, you may still help <g>) is I've a "temporary
relation" and a permanent relation.
A typical situation is the one of session in a web app and temporary basket.
Once the user log in, the temporary basket becomes "permanent".
But I'd like automatic garbage collection for all tables simply related to
sessions.
When a session get deleted all row that are *just* related to the session, but
not to the user, should be deleted.
Anyway I still need normal "User related" delete.
The partial solution I found need to distinguish what fired a trigger another
trigger or a "plain delete" and pass it on to the other triggers.
I've something similar:
create table T_Session (
idSession char(32) not null default md5(now() || random())
)
alter table T_Session
add constraint PK_Session
primary key (idSession)
;
create table T_User (
idUser integer
, idSession char(32) --should stay here or in a
t_Session_User(idSession,idUser)?
);
alter table T_User
add constraint PK_User
primary key (idUser)
;
create table T_Preferences (
idPreferences integer
, idSession char(32)
);
alter table t_Preferences
add constraint PK_Preferences
primary key (idPreferences)
;
alter table t_Preferences
add constraint FK_Preferences_Session
foreign key (idSession)
references t_Session (idSession)
on delete cascade
;
alter table t_Preferences
add constraint FK_Preferences_User
foreign key (idUser)
references t_User (idUser)
on delete cascade
;
create table t_Preferences_Stuff (
idStuff integer
, idPreferences integer
);
alter table T_Preferences_Stuff
add constraint PK_Preferences_Stuff
primary key (idStuff)
;
alter table T_Preferences_Stuff
add constraint FK_Preferences_Stuff
foreign key (idPreferences)
references T_Preferences (idPreferences)
on delete cascade
;
create or replace function TG_SessionDelete(
)
returns trigger as '
begin
if OLD.idUser is not null then
return null;
else
return OLD;
end if;
end;
' language plpgsql;
create trigger TG_SessionDelete before delete on T_Preferences
for each row execute procedure TG_SessionDelete();
Now if I delete a session with something like
delete from t_Session where idSession='..........';
all the row in the linked tables where the idUser is null should be deleted.
If the row has a not null idUser it shouldn't be deleted.
And this works.
But what if I'd like to obtain those behaviour too:
1)
delete from T_Preferences where idSession='....';
should delete all row in linked tables, no matter if idUser is null or not
I'm still thinking if this is actually what I want since if there is a link to
idUser I may use idUser to delete rows.
So I could split deletion related to session operations and deletion related to
user operations.
2)
delete from T_User where idUser=10;
delete from T_Preferences where idUser=37;
should delete all row in linked tables.
the above trigger doesn't work.
I've tried to understand if there is a way to exploit TG_ARGV[] & Co. but I
didn't understand even how to use it.
BTW postgres is 7.4.13
thx
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
