The followig trigger is an after insert trigger:

The 1st raise executes when not commented, so I know the trigger is firing.
The latter raise never gets reached, nor are errors generated.

This is my 1st trigger w/ EDB, so I'm pretty sure I'm doing something
boneheaded.

BEGIN
declare sql statement;

--RAISE ERROR CODE 10000 MESSAGE cast( newrow.idCorpId as varchar(38));


prepare sql from 'insert into tCorps_Sec
(idCorpId,idSecGrpId,intSecLevel)
values(?,?,0)';
execute sql using newrow.idCorpId,fnGetPrivateGrpId();
unprepare sql;

RAISE ERROR CODE 10000 MESSAGE cast( newrow.idCorpId as varchar(38));
END



jr

Resolved. I found my problem but it might also be a bug:

Using EDB 2.02.b10, errors in functions do not get raised when they =
occur in triggers. I've not tested it to see if they get raised in =
stored procs nor not.

This is the corrected syntax, and it works:
 BEGIN
 DECLARE curSel CURSOR FOR selUserId;
 declare idResult guid;

 prepare selUserId from 'select idSecGrpId from tSecGrps
 where idUserId=3DfnGetUserId()';
 open curSel;  =20
 if (rowcount(curSel)>0) then
    --only executes if the current DB user is also an app user
    fetch first from curSel(idSecGrpId) into idResult;
 end if;

 close curSel;

 unprepare selUserId;

 return idResult;
 end


Syntax with an obvious isue with the field name being fetched causes an =
error but does not get raised in triggers:
 BEGIN
 DECLARE curSel CURSOR FOR selUserId;
 declare idResult guid;

 prepare selUserId from 'select idSecGrpId from tSecGrps
 where idUserId=3DfnGetUserId()';
 open curSel;  =20
 if (rowcount(curSel)>0) then
    --only executes if the current DB user is also an app user
    fetch first from =
curSel(ddddddddddddddddddddddddddddddddddddddddddddd) into idResult;
 end if;

 close curSel;

 unprepare selUserId;

 return idResult;
 end


jr


"James Relyea" <JRelyea@JBRSoftware.com> wrote in message =
news:73D5FBD3-31DD-4A06-ABEE-2A3D8383524D@news.elevatesoft.com...
> The followig trigger is an after insert trigger:
>=20
> The 1st raise executes when not commented, so I know the trigger is =
firing.=20
> The latter raise never gets reached, nor are errors generated.
>=20
> This is my 1st trigger w/ EDB, so I'm pretty sure I'm doing something=20
> boneheaded.
>=20
> BEGIN
> declare sql statement;
>=20
> --RAISE ERROR CODE 10000 MESSAGE cast( newrow.idCorpId as =
varchar(38));
>=20
>=20
> prepare sql from 'insert into tCorps_Sec
> (idCorpId,idSecGrpId,intSecLevel)
> values(?,?,0)';
> execute sql using newrow.idCorpId,fnGetPrivateGrpId();
> unprepare sql;
>=20
> RAISE ERROR CODE 10000 MESSAGE cast( newrow.idCorpId as varchar(38));
> END
>=20
>
>=20
> jr
>=20
>

James,

Per email, this cannot be reproduced and is not considered a bug at this
point.

--
Tim Young
Elevate Software
www.elevatesoft.com

agreed

> this cannot be reproduced and is not considered a bug at this point.

I may need to rescind the "agreed". It's happening again. I'll follow up
tomorrow with you Tim assuming the gremlins hang around for the repros.




jr


"James Relyea" <JRelyea@JBRSoftware.com> wrote in message
news:BB94A9E0-97CC-400A-90A6-8B337134FFA3@news.elevatesoft.com...
> agreed
>
>> this cannot be reproduced and is not considered a bug at this point.
>
>
>
>

>I may need to rescind the "agreed". It's happening again.=20
>=20

This is a valid before update trigger for testing purposes. I know the =
trigger is firing because uncommenting the 1st raise command generates =
an exception.

Neither of the latter 2 ever fire, which can not be possible. There is a =
bug somewhere in b10, but my problem is that I'm having a real hard time =
reproducing it to send in with a sample DB because it's only =
sporadically happening.=20

Under another very interesting note, the server had no sessions, and I =
could not edit the trigger immediately after closing all open databases =
and disconnecting each session because the table was locked.


BEGIN
declare del statement;
--raise error code  10001 message 'hit it';  =20

if(user function that returns a #<=3D1) then
  /*executes if the user does not have admin/delete permissions.
    Denies the deletion */
  raise error code  10001 message 'Deletion Denied: Only "Owners" can =
change Security Levels.';  =20
else
  /*Executes if the user's permissions are OK to delete the corp
    Starts cleaning up all of the child records can also be updated  */ =

raise error code  10001 message cast (fnSec_MaxForCorp(oldrow.idCorpid) =
as varchar(10));
  ... whatever sql commands
end if; =20

END


jr

I'm going to email you some details Tim because I'm just not sure why this
keeps showing up.


jr

James,

<< This is a valid before update trigger for testing purposes. I know the
trigger is firing because uncommenting the 1st raise command generates an
exception.

Neither of the latter 2 ever fire, which can not be possible. There is a bug
somewhere in b10, but my problem is that I'm having a real hard time
reproducing it to send in with a sample DB because it's only sporadically
happening. >>

Well, I really can't comment based upon what you've posted due to the fact
that it isn't complete.  If you want to send me a sample database with the
complete trigger definition, I can comment further.

<< Under another very interesting note, the server had no sessions, and I
could not edit the trigger immediately after closing all open databases and
disconnecting each session because the table was locked. >>

Under what circumstances did this occur ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>=20
> Well, I really can't comment based upon what you've posted due to the =
fact=20
> that it isn't complete.  If you want to send me a sample database with =
the=20
> complete trigger definition, I can comment further.

Soon as I have something that I can reliably reproduce, I'll be more =
than happy to get it to you with the repro steps.

>=20
> << Under another very interesting note, the server had no sessions, =
and I=20
> could not edit the trigger immediately after closing all open =
databases and=20
> disconnecting each session because the table was locked. >>
>=20
> Under what circumstances did this occur ?

During my testing of the functions & triggers last night. I haven't seen =
it before, nor since. It did time out and let me continue though. If I =
happen to come accross this too, I'll update you. Honestly, I took that =
as a "what the? o'well it timed out", and I just continued on with my =
trigger work.



jr