stored procedure or trigger

Report · Apr 21, 2009

1. WHich is better, to use cfquery or cfstoredproc+a stored procedure ?

2. I've had luck getting @@rowcount from a sql server stored proc to cfoutput in a Cold Fusion template. How can the value of the record id for an updated or deleted record be cfoutputted in a ColdFusion template?

Report · Apr 21, 2009

What version of ColdFusion?

CF 8 now returns IDs from <cfquery...> operations. I've not used it enough to know forsure if this is for all operations such as delete and updates or only inserts.

lwfg wrote:
1. WHich is better, to use cfquery or cfstoredproc+a stored procedure ?

Neither is better and neither is worse and they both have a place in good code.

Report · Apr 21, 2009

lwfg wrote:
2. I've had luck getting @@rowcount from a sql server stored proc to cfoutput in a Cold Fusion template. How can the value of the record id for an updated or deleted record be cfoutputted in a ColdFusion template?

I would imagine that is database dependent, and I do not recall cfquery providing support for that. I believe it only returns the id for single record inserts. However, if you are using ms sql 2005+ you can use the OUTPUT clause. I do not know about other databases.

http://cfsearching.blogspot.com/2009/04/ms-sql-2005-more-on-underused-output.html

Edit: Other than that, triggers are the only other option I know of that is supported in most databases...