Re: [ADMIN] Do Something before Abort on Trigger ???

I'm going to CC this to the -general list as well, it's possible that -admin
isn't the right list for this thread.

You can not rollback or commit within a function. That would require nested
transactions which aren't implemented yet. Obviously that precludes use of your
'begin work' in your function.

All this means that you can not store your history records and have the
transaction rollback.

However, you are using a before trigger (I presume the created function name is
a typo below) so to store a history but not the new data you can return NULL
from your function. To proceed with the insert simply return NEW, with whatever
modifications of it's data you require.

So you have something along the lines of:

create function aaa ( ) returns trigger as '
begin
  insert into log_table values ( new.fn_value );
  if fn_value < 10  then
    insert into log_table values ( new.fn_value * 10 );
    return null;
  endif
  return new;
end
' language 'plpgsql';


Hope this helps you (and that I haven't made any nasty misdirection mistakes).


--
Nigel J. Andrews


On Fri, 17 Jan 2003, Yudha Setiawan wrote:

>
> ----- Original Message -----
> From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
> To: "Yudha Setiawan" <yudha@BonBon.net>
> Cc: <pgsql-admin@postgresql.org>
> Sent: Friday, January 17, 2003 3:40 PM
> Subject: Re: [ADMIN] Do Something before Abort on Trigger ???
>
>
> > Well you're aborting the transaction, your function is part of the
> transaction,
> > so any changes it makes to t_my_listerror will be aborted (or rolled
> > back). Ah, I see that you're raising the exception before you insert even.
> Why
> > would you expect control to reach the insert statement?
> >
> > I imagine the only remotely sensible way to achieve what you want is to
> log to
> > file. That would need a trusted language installed. I can't see using the
> > standard postgresql log output being terrible useful for you, besides, you
> seem
> > to be using the exception notice to return information somewhere.
> >
> > > --
> > Nigel J. Andrews
> >
> What big of thanks of me for you for the Adviced. It's a smart solutions but
> One things you should know, we don't wanna take a long time to Porting
> the Front-End. We have a lot of Job to be done and the "Time is Getting
> Closer".
> Our application is used to be taking a record from t_my_listerror for
> knowing
> the Error and then Showed to the User. We have a table for List Error Named
> T_BDE_Error. Before i reads this reply, I've tried this one;
>
> "
>   drop function fn_tr_bi_pulse() cascade;
>   create or replace function fn_tr_bi_yyy() returns trigger as '
>   begin
>       begin work;
>       insert into d_history.t_history_value (fn_value,fv_descript)
> values(new.fn_values,''For History'');
>       if (new.fn_value * 20) >= 1000  then
>          insert into d_history.t_history_value (fn_value,fv_descript)
> values(new.fn_values,''Invalid Values'');
>          rollback;
>       else
>          commit;
>       end if;
>       return new;
>   end;'  language 'plpglsql';
>   create trigger tr_bi_pulse before insert on d_transaction.t_pulse for each
> row
>             execute procedure fn_tr_bi_pulse();
> "
> There was no Error Show-up when i Compiling, but when i tried to inserting a
> record
> An Error is Show-Up. Do you have any ideas...??? Gimme your another best
> ones;
>