Problems due to @@IDENTITY

I have a database that sits on SQL Server 2000 and the client is Access XP
connecting to SQL via ODBC.
The database has been in use since November 2003 and all has been working
well. However......over the past w a strange thing has been happening
that, when new customer details were entered and the record was saved, the
displayed record would change to that of another although the new record had
been saved to the table.
At first I thought it was Access playing around (bless it) but have since
discovered the true cause. Whenever a new customer is entered, a SQL
trigger fires that will also create a dummy record in another table
(tbl_MainCaseEntry) ready for the user to enter details. There is an
essential reason for the trigger but it is too long winded to explain why.
The trigger reads:
CREATE TRIGGER trg_NewCustomer
ON tbl_Customer
FOR INSERT
AS
BEGIN
DECLARE @.CustID INT, @.CaseCount INT
SET @.CustID = (SELECT CustomerID FROM Inserted)
INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
END
The field CustomerID in tbl_MainCaseEntry is the foreign key with the table
having it's own primary key of MainID (set as an identity field seeded
(1,1)).
I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
back the last identity field for the entire statement (in this case the
MainID in tbl_MainCaseEntry for the new record created by the trigger).
Is there any way that I can stop this from happening by using
IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced back
to this value? I have tried to use SET @.@.IDENTITY =
IDENT_CURRENT('tbl_Customer') but this does not work.
Any advice would be appreciated as this is driving me mad!!
Regards
DazzaUse SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||Have you tried using SCOPE_IDENTITY already?
Look it up in BOL to learn more.
-Jason
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||look up scope_identity in BOL
"Dazza" <Post2Group@.Only.com> wrote in message
news:%233683HKMFHA.1096@.tk2msftngp13.phx.gbl...
>I have a database that sits on SQL Server 2000 and the client is Access XP
>connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
> happening that, when new customer details were entered and the record was
> saved, the displayed record would change to that of another although the
> new record had been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
> table having it's own primary key of MainID (set as an identity field
> seeded (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
> back to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||I have looked in the BOL regards this feature but cannot understand it's
exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what is
the syntax please?
All BOL seems to show is that it will display the identity of the table
where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY].
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Use SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dazza" <Post2Group@.Only.com> wrote in message
> news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> happening
> had
> table
> back
>|||@.@.IDENTITY is fine to use within the trigger.
But if you want the calling code to return the identity value generated by
its INSERT statement (not the INSERT in the trigger), use SCOPE_IDENTITY()
in the calling code. http://www.aspfaq.com/2174
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#kky3OKMFHA.2384@.tk2msftngp13.phx.gbl...
> I have looked in the BOL regards this feature but cannot understand it's
> exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what
is
> the syntax please?
> All BOL seems to show is that it will display the identity of the table
> where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY()
AS
> [SCOPE_IDENTITY].
> Regards
> Dazza
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
documented.
working
record
since
pulling
>|||I also note another problem here.
I guess you assume that inserts always happen in singleton? You need this
code to be multi-row aware. For example, watch what happens when you do
this:
INSERT Customers(col1, ..., colN)
SELECT 'col1', ..., colN
UNION
SELECT 'col1', ..., colN
To correct this, your INSERT statement inside the trigger should simply be:
INSERT tbl_MainCaseEntry (CustomerID)
SELECT CustomerID FROM Inserted
Or, change the calling stored procedure to handle the logging part of this,
and eliminate the need for a trigger at all. You do control access to this
table via stored procedures, right?
In any case, the calling app can't expect to get back a single @.@.IDENTITY or
SCOPE_IDENTITY() in the multi-row insert case.
(I also think you should consider dropping the superfluous tbl_ prefix, but
that's just an opinion.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aaron
Many thanks for the advice.
I do not use SPs for the entry or retrieval of the data in forms as the
front-end uses linked tables. I have been working with SQL Server for about
a year (mostly admin) and still have a lot to learn regards development
methods for front-end access clients. The database in question is my first
real production development project but there are more to come I have been
told !!
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PMTcUKMFHA.2420@.TK2MSFTNGP12.phx.gbl...
>I also note another problem here.
>
> I guess you assume that inserts always happen in singleton? You need this
> code to be multi-row aware. For example, watch what happens when you do
> this:
> INSERT Customers(col1, ..., colN)
> SELECT 'col1', ..., colN
> UNION
> SELECT 'col1', ..., colN
> To correct this, your INSERT statement inside the trigger should simply
> be:
> INSERT tbl_MainCaseEntry (CustomerID)
> SELECT CustomerID FROM Inserted
> Or, change the calling stored procedure to handle the logging part of
> this,
> and eliminate the need for a trigger at all. You do control access to
> this
> table via stored procedures, right?
> In any case, the calling app can't expect to get back a single @.@.IDENTITY
> or
> SCOPE_IDENTITY() in the multi-row insert case.
> (I also think you should consider dropping the superfluous tbl_ prefix,
> but
> that's just an opinion.)
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>