Trigger and @@IDENTITY problems

Trigger and @@IDENTITY problems

Post by Kinsley Ranso » Sun, 18 Mar 2001 01:36:14



Hi,


trigger on the table with the insert occuring. Does anyone know the way
around this problem, instead of me doing a select max(identity) from the
table in question. I'm using SQL 7.0.

Thanks in advance

 
 
 

Trigger and @@IDENTITY problems

Post by Andrew J. Kell » Sun, 18 Mar 2001 02:21:41


The only way around it I know of is to either don't use IDENTITIES or
upgrade to SQL 2000.

--
Andrew J. Kelly
Targitmail.com


> Hi,

> I've been trying to retrieve the identity value from an SP using the

> trigger on the table with the insert occuring. Does anyone know the way
> around this problem, instead of me doing a select max(identity) from the
> table in question. I'm using SQL 7.0.

> Thanks in advance


 
 
 

Trigger and @@IDENTITY problems

Post by Arthur Rakitski » Sun, 18 Mar 2001 03:05:53


    MAX doesn`t works properly through transactions, so...
    I think it helps :-) Try

-- Table with IDENTITIES

CREATE TABLE [dbo].[SysTablesIDENTITY] (
 [TableName] [sysname] NOT NULL ,
 [IDENTITY_Val] [int] NOT NULL ,
 [SPID] [smallint] NOT NULL ,
 [CreatedBy] [sysname] NOT NULL ,
 [CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

--For Your trouble table

CREATE TRIGGER [tA_I_Documents] ON [dbo].[Documents]
FOR INSERT
AS


SET NOCOUNT ON
INSERT INTO SysTablesIDENTITY (TableName, IDENTITY_Val, CreatedBy)
  SELECT 'Documents', i.Document_ID, 'tA_I_Documents'
  FROM INSERTED i
SET NOCOUNT OFF
GO


CREATE PROCEDURE spA_GetIDENTITY (




AS



ELSE

    FROM SysTablesIDENTITY i


    ORDER BY i.IDENTITY_Val DESC)

  BEGIN



    GOTO lb_err





    FROM syscolumns c INNER JOIN sysobjects o ON (o.id = c.id)


    GOTO lb_err





  OPEN curTemp

  CLOSE curTemp
  DEALLOCATE curTemp

    GOTO lb_err


  END
GOTO lb_end
lb_err:
RETURN 1
lb_end:


RETURN 0
GO


> Hi,

> I've been trying to retrieve the identity value from an SP using the

> trigger on the table with the insert occuring. Does anyone know the way
> around this problem, instead of me doing a select max(identity) from the
> table in question. I'm using SQL 7.0.

> Thanks in advance

 
 
 

Trigger and @@IDENTITY problems

Post by Arthur Rakitski » Sun, 18 Mar 2001 03:07:46


see the solution below :-) It was hard to me, to fight with MSSQL

> The only way around it I know of is to either don't use IDENTITIES or
> upgrade to SQL 2000.

> --
> Andrew J. Kelly
> Targitmail.com



> > Hi,

> > I've been trying to retrieve the identity value from an SP using the

> > trigger on the table with the insert occuring. Does anyone know the way
> > around this problem, instead of me doing a select max(identity) from the
> > table in question. I'm using SQL 7.0.

> > Thanks in advance

 
 
 

Trigger and @@IDENTITY problems

Post by Ken Cha » Wed, 21 Mar 2001 02:25:06


Dear Kinsley

There is a simple solution by Scott Coutant and Robertson Garcia.
FULL DETAIL can be found in www.sqlmag.com  article id = 9736  (Finding the
Lost Identity)

CREATE PROC ResetIdentity

AS




INTO #tIdent'


GO

Rewrite your trigger



[YOUR CODE...]


GO


> Hi,

> I've been trying to retrieve the identity value from an SP using the

> trigger on the table with the insert occuring. Does anyone know the way
> around this problem, instead of me doing a select max(identity) from the
> table in question. I'm using SQL 7.0.

> Thanks in advance

 
 
 

Trigger and @@IDENTITY problems

Post by Arthur Rakitski » Thu, 22 Mar 2001 22:15:53


    It will not work if exists more than one same triggers :(

> Dear Kinsley

> There is a simple solution by Scott Coutant and Robertson Garcia.
> FULL DETAIL can be found in www.sqlmag.com  article id = 9736  (Finding the
> Lost Identity)

> CREATE PROC ResetIdentity

> AS




> INTO #tIdent'


> GO

> Rewrite your trigger



> [YOUR CODE...]


> GO



> > Hi,

> > I've been trying to retrieve the identity value from an SP using the

> > trigger on the table with the insert occuring. Does anyone know the way
> > around this problem, instead of me doing a select max(identity) from the
> > table in question. I'm using SQL 7.0.

> > Thanks in advance

 
 
 

Trigger and @@IDENTITY problems

Post by Fabio Luiz Id » Fri, 23 Mar 2001 01:44:45


    That solution does work!

    But you have to include the call to ResetIdentity on every trigger for
insert on the desired table that has an INSERT statement inside it.

    []s

        Fabio Ide



>     It will not work if exists more than one same triggers :(


> > Dear Kinsley

> > There is a simple solution by Scott Coutant and Robertson Garcia.
> > FULL DETAIL can be found in www.sqlmag.com  article id = 9736  (Finding
the
> > Lost Identity)

> > CREATE PROC ResetIdentity

> > AS




> > INTO #tIdent'


> > GO

> > Rewrite your trigger



> > [YOUR CODE...]


> > GO



> > > Hi,

> > > I've been trying to retrieve the identity value from an SP using the

a
> > > trigger on the table with the insert occuring. Does anyone know the
way
> > > around this problem, instead of me doing a select max(identity) from
the
> > > table in question. I'm using SQL 7.0.

> > > Thanks in advance

 
 
 

1. Trigger problem w/ @@Identity...Please Help!!

O.k., working on Access 2000 ADP <-> SQL Server 7.0:
I have a form which is bound to a view..the underlying table (T1) in
the view has a trigger which inserts data into another table (T2)
based on the inserted values..

instead of recieving the correct identity for T1 it actually recieves
the new identity for T2 and applies it to the form as the T1 record
identity. now if i go back and change any data on the T1 record, the
resync command goes and an error pops up becuase of the invalid
identity value.
if anyone understands this or has run into it please help...
thanks,
jon

2. Upgrade 713 to 816

3. Problem with @@identity and trigger

4. SQL =, ==, LIKE

5. Identity and trigger - wonder problem

6. updating form when table fields have been changed

7. problem with @@identity and trigger

8. select failure

9. Trigger (insert) and @@identity problem...

10. IDENTITY column problems with triggers

11. Passing identity values outside triggers

12. help! @@IDENTITY and nested triggers