> I posted this before but never got a response. Has anybody
> been able to figure out how to get trigger results back through
> cfprocs? If I do something like this:
>
> <CFQUERY NAME="MyQuery" DATASOURCE="#MyDSN#" DBTYPE="ODBC">
> { CALL sp_MyStoredProc(#Int(MyInput)#) }
> </CFQUERY>
> <CFDUMP VAR="#MyQuery#">
>
> The results of whatever triggers run can be seen in the dump
> - like Audit_ID will be there if their is some audit trail being
> run on the table that sp_MyStoredProc is affecting, for instance.
> But, if I do something like this:
>
> <CFSTOREDPROC PROCEDURE="sp_MyStoredProc" DATASOURCE="#MyDSN#">
> <CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER"
> DBVARNAME="@MyInput" VALUE="#MyInput#">
> </CFSTOREDPROC>
>
> How do I see the results of whatever triggers run? What if I know
> that Audit_ID is the name of the field that the trigger returns?
> Understand that the Audit_ID field is never mentioned in
> sp_MyStoredProc - it's the output of a separate trigger
> (sp_MySeparateTrigger).
Have you tried using a CFPROCRESULT tag within your CFSTOREDPROC? It
shouldn't make a difference whether the recordset is explicitly drawn from
the stored procedure, or from the execution of a trigger launched by the
stored procedure.
In any case, from a design perspective, you might want to rethink your use
of triggers with stored procedures. Triggers are best used when you can't
use stored procedures - they allow you to write constraining code that will
be used with any SQL query run against the specified object, so if you're
allowing any SQL passthrough, triggers make sense. If you're able to use
stored procedures for all SQL within the database, you really don't need
triggers, since you can place the appropriate code within the stored
procedure itself.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
