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