Thread: Do Something before Abort on Trigger ???

On Wed, Jan 15, 2003 at 04:08:40PM +0700, Yudha Setiawan wrote:
> Somebody gimme your hand plz.
>
> Using my previous Database I used to be like that;
> " ALTER TRIGGER tr_T_DtlPO ON dbo.T_DtlPO FOR UPDATE AS
>   bla..bla..bla...
>   IF @OldQty <> @NewQty BEGIN
>      ROLLBACK
>      INSERT INTO T_My_ListError(fc_code,fv_descript)
>                VALUES('12345','No Authority to Change Qty')
>   END"
> So I'v already write the error code to table T_My_ListError before aborting this session
> -----------------------------------------------------------------------------------------
> And I Tried on Postgre like that;
> " create or replace function fn_tr_t_dtlpo returns trigger as'
>   begin
>      bla..bla...;
>      if new.fn_qty != old.fn_qty then
>         raise exception ''Error 12345'';
>         insert into t_my_listerror(fc_code,fv_descript)
>                  values(''12345'',''No Authority to Change Qty'');
>      end if;
>   end;' language 'plpgsql';
> "
> But I Couldn't get any records at all on t_my_listerror. Even when put the insert statement
> (insert into t_my_listerror...bla..bla) before raise exception.

Hmm, I think you may be out of luck, if you really _have_ to abort the
transaction, since PostgreSQL has no way to violate the transactional
integrity: anything you do inside a function that's in a transaction
that fails will rollback. Alternatively, you can supress the UPDATE
by setting new.fn_qty = old.fn_qty, INSERTing a line in your errors
table, and even firing off a NOTICE so an LISTENing frontend can tell
the user about the problem. This will let the rest of the transaction
commit, however.

Sorry,
Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx./rice.edu      fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

On Wed, 15 Jan 2003, Yudha Setiawan wrote:

> Somebody gimme your hand plz.
>
> Using my previous Database I used to be like that;
> " ALTER TRIGGER tr_T_DtlPO ON dbo.T_DtlPO FOR UPDATE AS
>   bla..bla..bla...
>   IF @OldQty <> @NewQty BEGIN
>      ROLLBACK
>      INSERT INTO T_My_ListError(fc_code,fv_descript)
>                VALUES('12345','No Authority to Change Qty')
>   END"
> So I'v already write the error code to table T_My_ListError before aborting this session
> -----------------------------------------------------------------------------------------
> And I Tried on Postgre like that;
> " create or replace function fn_tr_t_dtlpo returns trigger as'
>   begin
>      bla..bla...;
>      if new.fn_qty != old.fn_qty then
>         raise exception ''Error 12345'';
>         insert into t_my_listerror(fc_code,fv_descript)
>                  values(''12345'',''No Authority to Change Qty'');
>      end if;
>   end;' language 'plpgsql';
> "
> But I Couldn't get any records at all on t_my_listerror. Even when put the insert statement
> (insert into t_my_listerror...bla..bla) before raise exception.

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

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