Do Something before Abort on Trigger ??? 
Author Message
 Do Something before Abort on Trigger ???

This is a multi-part message in MIME format.

------=_NextPart_000_0015_01C2BE01.C02E4DE0
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Somebody Help Me.....

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...

     ROLLBACK
     INSERT INTO T_My_ListError(fc_code,fv_descript)=20
               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 !=3D old.fn_qty then=20
        raise exception ''Error 12345'';
        insert into t_my_listerror(fc_code,fv_descript)=20
                 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.

------=_NextPart_000_0015_01C2BE01.C02E4DE0
Content-Type: text/html;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3D"Courier New" size=3D2>Somebody Help Me.....</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Using my previous Database I used =
to be=20
like that;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>" ALTER&nbsp;TRIGGER tr_T_DtlPO&nb=
sp;ON=20
dbo.T_DtlPO FOR UPDATE AS</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;bla..bla..bla...</FONT=

Quote:
></DIV>


ewQty=20
BEGIN</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;=20
ROLLBACK</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INSE=
RT=20
INTO&nbsp;T_My_ListError(fc_code,fv_descript) </FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;=20
VALUES('12345','No Authority to Change Qty')</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp; EN</FONT><FONT face=3D"Cour=
ier New"=20
size=3D2>D"</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>So I'v already write the error cod=
e to=20
table T_My_ListError before aborting this session</FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>------------------------------------------------------------------=
-----------------------</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>And I Tried on Postgre like=20
that;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>" create or replace function fn_tr=
_t_dtlpo=20
returns trigger as'</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp; begin</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;=20
bla..bla...;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp; if new.fn=
_qty !=3D=20
old.fn_qty then </FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
raise exception ''Error 12345'';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
insert into t_my_listerror(fc_code,fv_descript) </FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
values(''12345'',''No Authority to Change Qty'');</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end=
=20
if;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;end;' language=20
'plpgsql';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>"</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>But I Couldn't get any records at =
all on=20
t_my_listerror. Even when put the insert statement</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>(insert into t_my_listerror...bla.=
.bla)=20
before raise exception.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Some advice will be very helpfull =
to me.=20
Thank's a lot and ...</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>GOD Bless You All.</FONT></DIV>
<DIV>&nbsp;</DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_0015_01C2BE01.C02E4DE0--



Tue, 05 Jul 2005 09:40:20 GMT
 Do Something before Abort on Trigger ???

This is a multi-part message in MIME format.

------=_NextPart_000_023A_01C2BCB0.5EBA0330
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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...

     ROLLBACK
     INSERT INTO T_My_ListError(fc_code,fv_descript)=20
               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 !=3D old.fn_qty then=20
        raise exception ''Error 12345'';
        insert into t_my_listerror(fc_code,fv_descript)=20
                 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.

------=_NextPart_000_023A_01C2BCB0.5EBA0330
Content-Type: text/html;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Courier New" size=3D2>Somebody gimme your hand plz.</FON=
T></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Using my previous Database I used =
to be=20
like that;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>" ALTER&nbsp;TRIGGER tr_T_DtlPO&nb=
sp;ON=20
dbo.T_DtlPO FOR UPDATE AS</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;bla..bla..bla...</FONT=

Quote:
></DIV>


ewQty=20
BEGIN</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;=20
ROLLBACK</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INSE=
RT=20
INTO&nbsp;T_My_ListError(fc_code,fv_descript) </FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;=20
VALUES('12345','No Authority to Change Qty')</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp; EN</FONT><FONT face=3D"Cour=
ier New"=20
size=3D2>D"</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>So I'v already write the error cod=
e to=20
table T_My_ListError before aborting this session</FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>------------------------------------------------------------------=
-----------------------</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>And I Tried on Postgre like=20
that;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>" create or replace function fn_tr=
_t_dtlpo=20
returns trigger as'</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp; begin</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;=20
bla..bla...;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp; if new.fn=
_qty !=3D=20
old.fn_qty then </FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
raise exception ''Error 12345'';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
insert into t_my_listerror(fc_code,fv_descript) </FONT></DIV>
<DIV><FONT face=3D"Courier New"=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
values(''12345'',''No Authority to Change Qty'');</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end=
=20
if;</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;end;' language=20
'plpgsql';</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>"</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>But I Couldn't get any records at =
all on=20
t_my_listerror. Even when put the insert statement</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>(insert into t_my_listerror...bla.=
.bla)=20
before raise exception.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Some advice will be very helpfull =
to me.=20
Thank's a lot and ...</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>GOD Bless You All.</FONT></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_023A_01C2BCB0.5EBA0330--



Tue, 05 Jul 2005 14:00:51 GMT
 Do Something before Abort on Trigger ???

Quote:

> 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...

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

Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx./rice.edu      fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Tue, 05 Jul 2005 23:42:34 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Do Something before Abort on Trigger ???

2. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

3. TStringList - I'm doing something wrong here - HELP

4. Has this been done before or have I found something new

5. Help - I have done something rather stupid

6. Is access really doing something else ???

7. Am I doing something wrong...??

8. Am I doing something wrong...??

9. problem with doing something like this...

10. I think I am doing something wrong

11. Trigger silently aborts?!?


 
Powered by phpBB® Forum Software