what fired a trigger

[Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx>]

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