what fired a trigger

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