Sorry, my friend, but you are wrong here. Triggers can contain hundreds of
lines of complicated code to enforce complex user-defined business rules,
and they can contain SELECT statements and also return result sets (in
T/SQL)-- which can be configured to look like parameters if the result set
is a single row.
I lectured on this at CFUN2K last month, and our upcoming high-end
ecommerce product uses these techniques extensively to create a very robust
and scalable app.
The meat of the issue is this: inserting a table row from a stored
procedure, then gaining access to the Indentity column value just inserted.
The trigger Neil shows will indeed produce a result that can be accessed
in CFML after CFQUERY is run by using QueryName.ColumnName. The problem
is: how do you access this from a stored procedure that inserts the row?
The answer is this: reference @@IDENTITY in the stored procedure
IMMEDIATELY after the statement that inserts the table row. This won't
make use of the trigger's SELECT result, but it will give you the value of
the Identity column most recently inserted into a table.
BTW, David and I will be teaching this and many other techniques during
next week's Ecommerce Development with ColdFusion seminar in Memphis. If
you want a half-price pass, then just email a request to
[EMAIL PROTECTED], but hurry because today is the final
day of registration.
Hope this helps. Cheers! :)
At 01:42 PM 8/25/00 +0100, you wrote:
>quite right....a trigger cannot contain a select and therefore cannot return
>a resultset or return a parameter
>
>-----Original Message-----
>From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
>Sent: 25 August 2000 11:38
>To: '[EMAIL PROTECTED]'
>Subject: RE: SQL Trigger / Stored Proc question
>
>
>> > I don't think you can pass a value out from a trigger - once the trigger
>>
>> sure you can. you can reference its value in queryName.mytable_ID
>
>But what is the query that queryName refers to?
>
>If you say <CFQUERY...> CREATE TRIGGER...AS SELECT mytable_ID...</CFQUERY>
>
>then what is executed is the CREATE TRIGGER statement, i.e. it stores the
>trigger in the database. The trigger only *fires* when a row is inserted.
>Are you saying that CF is clever enough to pick up the fact that the
>trigger has fired & grab the value from somewhere?
>
>The SQL Server documentation says:
>
><QUOTE>
>Triggers can include any number and kind of Transact-SQL statements except
>SELECT. A trigger is designed to check or change data based on a data
>modification statement; it should not return data to the user.
></QUOTE>
>...
><QUOTE>
>To eliminate having results returned to an application due to a trigger
>firing, do not include either SELECT statements that return results, or
>statements that perform variable assignment in a trigger. A trigger that
>includes either SELECT statements that return results to the user or
>statements that perform variable assignment requires special handling; these
>returned results would have to be written into every application in which
>modifications to the trigger table are allowed.
></QUOUTE>
>
>I think encapsulating it all in a stored proc would be the best thing.
>
>Nick
>
>
>**********************************************************************
>Information in this email is confidential and may be privileged.
>It is intended for the addressee only. If you have received it in error,
>please notify the sender immediately and delete it from your system.
>You should not otherwise copy it, retransmit it or use or disclose its
>contents to anyone.
>Thank you for your co-operation.
>**********************************************************************
>----------------------------------------------------------------------------
>--
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
>------------------------------------------------------------------------------
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
Respectfully,
Adam Phillip Churvis
President
Productivity Enhancement, Inc.
****************************************************************************
* PRODUCTIVITY ENHANCEMENT, INC. *
* *
* Publishers of the CommerceBlocks line of modular development tools *
* for ColdFusion *
* *
* Website: http://www.commerceblocks.com E-mail: [EMAIL PROTECTED] *
* Phone: 770-446-8866 Fax: 770-242-0410 *
* *
****************************************************************************
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
