Обсуждение: Do Something before Abort on Trigger ???
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.
Some advice will be very helpfull to me. Thank's a lot and ...
GOD Bless You All.
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
Thank's so much to all of you guys. You have
giving me completely explainations about the
transactional on Postgre-SQL especially to
Nigel J. Andrews <nandrews@investsystems.co.uk> and
Ross J. Reedstrom <reedstrm@rice.edu>.
GOD Bless U All.
Yudha Setiawan.
VoIP;
No. : 01119311223344
Gatekeeper : 202.43.162.189
VoIP;
No. : 01119311223344
Gatekeeper : 202.43.162.189
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; >