Jason, You're thinking that the parser won't bother checking that far down?
Darryl Lyons http://dangermoose.blogspot.com [EMAIL PROTECTED] wrote on 16/03/2005 11:05:31 AM: > Maybe take the sql code that the trigger is not suppose to process > and throw it into another stored procedure. Then in the original sp > you can check by the flags to see if you want to run the new sp. > This may be a work around for the problem but I don't know how much > more executed time overhead this may add. I think this should stop > the sql parser from throwing the error (I could be wrong as I > haven't tested, just an idea). > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of TRACEY, > Darren > Sent: Tuesday, 15 March 2005 5:04 PM > To: CFAussie Mailing List > Subject: [cfaussie] SOT: MSSQL Trigger/Stored Proc/Temp Table problem > > > Hi all. > This is a problem that one of the other people at Suncorp had with some SQL > that eventually gets called from some CF code. > I've got a Stored Procedure that gets called from a whole bunch of places, > including a few triggers. > Code has been added to the stored proc that makes a temporary table, does > some processing and returns some values. > A flag is passed to tell it whether these new return values are required. > This flag is never set to indicate that return values are required in the > cases where the stored Proc is called from a trigger. > Logic in the Stored proc bypasses all the code that creates, uses and > removes the temorary table, when the flag does not indicate that returned > values are required. > Everything workls fine until the stored proc is called by a trigger, upon > which it returns this error: > (this was produced from MSSQL Enterprise manager) > > UPDATE TTxnTIdeaAcct SET SavedAmt = 1200 > WHERE IdeaAcctIDNo = 26840 > > Produces: > > > (66 row(s) affected) > > Server: Msg 287, Level 16, State 2, Procedure ap_PhasingAmountCalc, Line 216 > The CREATE TABLE statement is not allowed within a trigger. > Server: Msg 287, Level 16, State 1, Procedure ap_PhasingAmountCalc, Line 374 > The DROP TABLE statement is not allowed within a trigger. > > The logic in the stored proc should not allow any of this code to be run, > yet SQL seems to be parsing the code irespective of logic flow. > The logic has been changed to 'if 1=0' around the temporary table creation > code, so that there can be no way that it can be run, yet it still causes > this error. > > Does anyone know of some clever way that this can be made to work, or do I > need to go back to the drawing board? > > Regards > > Darren Tracey > Systems Analyst > HR Systems and FastTrack, Web and Integration Services > p: + 61 7 3232 4091 (x64091) > f: + 61 7 3232 4744 > e: [EMAIL PROTECTED] > l: Lvl 3, 388 Queen St Brisbane QLD 4000 > m: Suncorp IPC IT048, GPO Box 1453, Brisbane QLD 4000 > > > > > ----------------------------------------------------------------------------------- > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or > one of its related entities ("Suncorp"). > > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane > or on 13 11 55 or at suncorp.com.au. > > The content of this e-mail is the view of the sender or stated > author and does not necessarily reflect the view of Suncorp. The > content, including attachments, is a confidential communication > between Suncorp and the intended recipient. If you are not the > intended recipient, any use, interference with, disclosure or > copying of this e-mail, including attachments, is unauthorised and > expressly prohibited. If you have received this e-mail in error > please contact the sender immediately and delete the e-mail and any > attachments from your system. > > If this e-mail constitutes a commercial message of a type that you > no longer wish to receive please reply to this e-mail by typing > Unsubscribe in the subject line. > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > Aussie Macromedia Developers: http://lists.daemon.com.au/ > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > Aussie Macromedia Developers: http://lists.daemon.com.au/ To unsubscribe from this email please forward this email to [EMAIL PROTECTED] This email message is confidential and may be privileged. If you are not the intended recipient please forward the email to [EMAIL PROTECTED] and delete the original. ABN AMRO Morgans Limited and its associates hold or may hold securities in the companies/trusts mentioned herein. Any general advice included in this email has been prepared without taking into account your objectives, financial situation or needs. Before acting on the advice, you should consider its appropriateness or discuss with one of our investment advisors. ABN AMRO Morgans Limited (ABN 49 010 669 726 AFSL 235410) A Participant of ASX Group. A principal member of the Financial Planning Association. --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
