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/
