Re: Do Something before Abort on Trigger ???

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