Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Trigger problem |
Fri, Apr 17 2009 4:05 PM | Permanent Link |
"James Relyea" | 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 |
Fri, Apr 17 2009 4:27 PM | Permanent Link |
"James Relyea" | 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 > |
Tue, Apr 21 2009 1:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
Per email, this cannot be reproduced and is not considered a bug at this point. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 22 2009 9:27 PM | Permanent Link |
"James Relyea" | agreed
> this cannot be reproduced and is not considered a bug at this point. |
Wed, Apr 22 2009 10:16 PM | Permanent Link |
"James Relyea" | 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. > > > > |
Tue, Apr 28 2009 11:04 PM | Permanent Link |
"James Relyea" | >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 |
Wed, Apr 29 2009 6:55 AM | Permanent Link |
"James Relyea" | I'm going to email you some details Tim because I'm just not sure why this
keeps showing up. jr |
Wed, Apr 29 2009 2:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Apr 29 2009 4:21 PM | Permanent Link |
"James Relyea" | >=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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |