scotts suggestion is good - its not a matter of whether those lines of code run as a result of the trigger - the compiled sproc called by the trigger contains ddl statements which are not allowed.
Elliot "Darryl Lyons" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Learn something new every day. > > Darryl > > [EMAIL PROTECTED] wrote on 16/03/2005 07:17:29 AM: > > > perhaps you could use a variable table, eg > > > > declare @temp_table table (idfield integer, someotherfield varchar(10)) > > select * from @temp_table > > > > Scott Thornton, Programmer > > Application Development > > Information Services and Telecommunications > > Hunter-New England Area Health Service > > Phone RNH +61 2 49236078 JHH +61 2 49214193 > > Fax +61 2 49214191 > > > > [EMAIL PROTECTED] > > >>> [EMAIL PROTECTED] 03/15/05 6:04 PM >>> > > 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/
