Re: SQL Trigger / Stored Proc question
> then you can refer to #insertRow.table1_ID# and it will > contain the ID that was inserted for you by SQL Server, > despite no such variable/column being mentioned in the > , because the SQL Server ODBC driver returns the yes. that value is created by sql server & passed back to whatever app called it. -- 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.
RE: SQL Trigger / Stored Proc question
I always like to use explicit references if possible-- @@IDENTITY is used here because it is the only way to do this. "Faster" in terms of time difference between these two techniques should not even be a consideration because, for all intents and purposes, it is negligible, and there are countless other better opportunities in any app to pick up speed. Hope this helps. Well, it's time for me to feed my trusty horse, Gefilte, so I simply must go... At 02:39 PM 8/25/00 +0100, you wrote: >I stand corrected!...thanks. > >Out of curiosity - When faced with this sort of thing I always reference the >INSERTED table to get the ID rather than @@IDENTITY. I know it will be >marginal if anything but will @@IDENTITY be quicker? are there any other >advantages or is it essentially the same thing? 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-8866Fax: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.
RE: SQL Trigger / Stored Proc question
I stand corrected!...thanks. Out of curiosity - When faced with this sort of thing I always reference the INSERTED table to get the ID rather than @@IDENTITY. I know it will be marginal if anything but will @@IDENTITY be quicker? are there any other advantages or is it essentially the same thing? -Original Message- From: Adam Phillip Churvis [mailto:[EMAIL PROTECTED]] Sent: 25 August 2000 14:33 To: [EMAIL PROTECTED] Subject: RE: SQL Trigger / Stored Proc question 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 righta 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 CREATE TRIGGER...AS SELECT mytable_ID... > >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: > > >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. > >... > >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. > > >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
RE: SQL Trigger / Stored Proc question
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 righta 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 CREATE TRIGGER...AS SELECT mytable_ID... > >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: > > >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. > >... > >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. > > >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* *
RE: SQL Trigger / Stored Proc question
quite righta 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 CREATE TRIGGER...AS SELECT mytable_ID... 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: 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. ... 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. 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.
RE: SQL Trigger / Stored Proc question
Paul, > triggers do have their place in development toolboxes. Sure, I never said otherwise. Triggers are great. I've built systems that rely completely on them. > > But what is the query that queryName refers to? > > the cfquery for the original insert. > > > Are you saying that CF is clever enough to pick up the fact > > that the trigger has fired & grab the value from somewhere? > > no, but ODBC & sql server are... So let me get this straight, if you have a trigger in your database like this: CREATE TRIGGER table1_insert ON table1 FOR INSERT AS SELECT table1_ID FROM INSERTED and have a .CFM containing this: INSERT into table1 (col2) VALUES (#value2#) then you can refer to #insertRow.table1_ID# and it will contain the ID that was inserted for you by SQL Server, despite no such variable/column being mentioned in the , because the SQL Server ODBC driver returns the trigger variable in response to the INSERT statement and CF puts that in the results accordingly? If so... cool! 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.
Re: SQL Trigger / Stored Proc question
lets nip this myth in the bud > But what is the query that queryName refers to? the cfquery for the original insert. > Are you saying that CF is clever enough to pick up the fact that the > trigger has fired & grab the value from somewhere? no, but ODBC & sql server are... > The SQL Server documentation says: since we're quoting (BoL): "When a trigger fires, results are returned to the calling application, just as with stored procedures." > > 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. > where does this bit come from? BoL only *suggests* you refrain from returning results w/triggers because it would require all applications accessing that table be able to handle the returned results. > > 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 you've taken this out of context. see the 1st line for this "quote" above. triggers do have their place in development toolboxes. -- 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.
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 CREATE TRIGGER...AS SELECT mytable_ID... 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: 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. ... 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. 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.
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 -- 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.
RE: SQL Trigger / Stored Proc question
> CREATE TRIGGER GetMax_ID ON mytable > FOR INSERT > AS > SELECT mytable_ID FROM INSERTED > > How would I reference the result within a stored procedure to insert > the "mytable_id" in the next query? Neil I don't think you can pass a value out from a trigger - once the trigger is created it exists independently of the rest of your code. But you could put all the logic inside the trigger, or have the trigger call a procedure with all the logic inside it. 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.
