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(at)rice(dot)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