Thread: 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
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

On Tue, 5 Sep 2006 22:53:14 +0200
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

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

OK let's see if I can make the description of the problem more general.

on delete cascade is comfortable because every time someone will add a new Session related table he just has to
rememberto add it making the garbage collection a "local" problem. 
Adding a rule on delete of session table will require modification to the rule every time a new table is added; unless
Ican think of a very smart way that will look into system tables[*] etc... but I suspect this will be terribly
inefficient.

A trigger on delete doesn't have any information about how it was called, it seems to me it can just know on which
tableit was fired and which field was deleted from the TG_NAME eventually, but considering that more than one trigger
maybe called for the same row, and the first one is determined by alphabetical order, it doesn't seem too useful. 
Arguments can't be passed because triggers are fired "indirectly". That means that a trigger may cause a delete that
willfire another trigger. The delete won't pass the argument of the previous trigger to the next. 

A possible workaround could be to use an update trigger to delete sessions/users.
A trigger may be linked to the update of the idUser, stored in the session table.
Updates that set to null a idUser are "alias" for delete everything unconditionally.
A delete... may be a conditional delete.
On every idSession/idUser table I'll have 2 triggers.

One on delete idSession. This one should be similar to the previous one I wrote.

One on update idUser should be something like

if NEW.idUser is null then
begin
--    delete from TG_RELNAME where idSession=OLD.idSession;
    delete from TG_RELNAME where idUser=OLD.idUser; --??
end;
return NEW;

I'm still considering if this approach is feasible and if it has any drawback.
Surely a bit more overhead to unset idUser when anyway the row is going to be deleted and the risk I'll have to
"delete"by idSession since the idUser get lost (I've to play with after/before). 
It doesn't seem too elegant and my main concern is it doesn't look flexible/general enough.


/****************
Anyway I'm still interested to know if there is any system to pass parameters between triggers or at least to know what
causeda trigger (a direct operation or a cascade operation). 
If anyone can enlighten me I'd be grateful.
*****************/



[*] eg. find in the system table all the tables that have an idSession column, use a cursor, a dynamically generated
statementand delete unconditionally on tables that don't have idUser, and conditionally (idUser is null) on table that
haveit. 
If there are tables where idSession is not unique this could be more efficient than triggers. But this won't be the
mostcommon case and dynamically generated statements and cursors will be expensive. 



--
Ivan Sergio Borgonovo
http://www.webthatworks.it