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/
