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