Posted by Erland Sommarskog on 07/02/05 01:16

Wolfgang Kreuzer (wolfgang.m.kreuzer@gmx.de) writes:
> I try to create a trigger on table Projects (INSERT) which
> automatically creates the top-level-entry in ProjectStruct but I
> didn't succed.
>
> Tried to use (several variations similar to)
>
> INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)
> SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))
> FROM INSERTED

Note that IDENT_CURRENT returns a value that is server-global, and
thus may be affected by the activity of other process. You proabably
do not want this one where.

> but this inserts a reference to the last inserted record. Why this
> happens is pretty clear to me, but I found no way to get the reference
> to the identity column of the record currently inserted.
>
> Is there a way to do this?

Nope, not in the dead end you are now.

The best is probably to remove the IDENTITY property from the table
and roll your own. That's pretty easy:

DECLARE @newids (ident int IDENTITY,
prjProjectID int NOT NULL)
DECLARE @nextid int
SELECT @nextid = coalesce(MAX(ProjectStructID), 0) + 1
FROM ProjectStruct WITH (UPDLOCK)

INSERT (prjProjectID) SELECT prjProjectID FROM inserted

INSERT ProjectStruct (ProjectId, PrjStructName, ParentId)
SELECT projProjectID, 'top level', @nextid + ident - 1
FROM @newids

The table variable is needed to cater for multi-row inserts.

A more common approach, though, is to leave the parent NULL for
the top-level object.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]