@@Identity

[William \(Bill\) Vaughn]

Experts: Please don't suggest @@Identity to anyone without understanding and
explaining the implications of this advice. While JET is stuck with
@@Identity, it's relatively safe, but the continued use of @@Identity with
SQL Server is questionable at best. While there are some special
circumstances where SCOPE_IDENTITY() isn't the best approach, it should be
recommended as a "first choice". As you know, if a trigger fires or other
code executes that changes a row in any table, the @@Identity value will
change and return a bogus value with potentially disastrous effect. This
might not be a factor for simple systems, but as developers make their
applications more sophisticated, they might inadvertently add a trigger or
other code that will alter the behavior of any number of stored procedures.

Thanks for helping.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Mark Rae]

SQL Server is questionable at best. While there are some special
circumstances where SCOPE_IDENTITY() isn't the best approach, it should be
recommended as a "first choice". As you know, if a trigger fires or other
code executes that changes a row in any table, the @@Identity value will
change and return a bogus value with potentially disastrous effect. This
might not be a factor for simple systems, but as developers make their
applications more sophisticated, they might inadvertently add a trigger or
other code that will alter the behavior of any number of stored
procedures.

Absolutely correct.

 

[AMDRIT]

Hold on

"..but as developers make their applications more sophisticated, they might
inadvertently add a trigger other code that will alter the behavior of any
number of stored procedures."

This here is the wrong reason to omit @@Identity from text and discussion.

Software design failures are mainly the result of poor planning and weak
documentation. There is a reason for If..Else..Then and Select Case, and
there is a purpose for @@Identity and SCOPE_IDENTITY().

A little on triggers, they are after the fact and issue generators in their
own right. Business logic should not be done there. Specialized business
code should be the core the enterprise. These are my own opinions, of
course, but I think they prevent inadvertent issues as you are describing.
My view is if you can't debug/trace it, why build it?

Experts hold that title because they are fluid with the technology and have
gone through the pains of discovery. Every child must skin their knees
after falling off their bike to understand why they should wear knee pads.

There is nothing wrong with the use of @@Identity, as long as you understand
what is going on. No matter if you are doing batch processing via DTS, bulk
inserting from external systems, general data entry, reporting and analysis,
or error correction, you should instill upon us lesser beings the importance
of viewing the bigger picture, creating utilities that use the business
objects we spend months creating and bottom line stay away from raw tables
and cascaded after the fact DML operations.

To use Scope_Identity as a replacement for @@Identity, you would force a
restriction of single row operations in a stored procedure to get back the
single Identity Column that you would normally expect. If you are going to
do that, then why fight it and just use @@Identity and restrict the use of
triggers?

Do it your way, that is fine by me. I still think that the easier path in
the long run is to reduce triggers and maximize use of business objects.

Would you have use stop using transactions because a developer could type
Commit/Rollback Trans in a trigger? It more about thinking about what you
are doing and be aware of what you affect, the role you play in the process.
It doesn't matter if you are a man show, a small group that work as islands,
or dedicated department work on a subsection of the bigger goal. Think,
Plan, Test, Think, Sleep.

Software sophistication does not require more internal complexity of code,
it requires a clear thought process and plan to achieve complicated tasks.

 

[Bob]

It doesn't matter if you are a man show, a small group that work as

islands, or dedicated department work on a subsection of the bigger goal.
Think, Plan, Test, Think, Sleep

Sleep?. Whats that?

Bob

 

[Bob]

AMDRIT:

To use Scope_Identity as a replacement for @@Identity, you would force a
restriction of single row operations in a stored procedure to get back the
single Identity Column that you would normally expect.

Wholy crap. I wish I knew that months ago. Thanks for the tip

I used all drag and drop sqlDataAdapters in my project and build the
relationships in the Schema designer.

I then bound my controles to the child tables by drilling down through the
relationships in the properties window(No DataGrid).

ClientsMain
relClientsMainClientsPage01
ClosingDate
ClosingFee

When the sqlDataAdapter.Fill(objdsClients)

It pages through each record just find, Thats is, the windows form shows the
correct child record for each parent but update fails with no error. It
almost seems like there are two datasets loading for the child table.

Public Sub LoadData()

objdsClients.EnforceConstraints = False

Me.SqlConnection1.Open()

daClientsMain.SelectCommand.Parameters("@ClientID").Value =
CInt(txtFind.Text)

Me.daClientsMain.Fill(objdsClients)

Me.daPage01.Fill(objdsClients)

'objdsClients.EnforceConstraints = True <--If I remove the comment out, it
gives me a constraint error but loads fine

Me.SqlConnection1.Close()

End Sub

TIA


Bob