what fired a trigger - Mailing list pgsql-general

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
onto 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