INSTEAD OF Insert Trigger and @@IDENTITY

Question

Post reply

INSTEAD OF Insert Trigger and @@IDENTITY

Robby Dillard

SSC Veteran

Points: 261
More actions
November 21, 2005 at 7:38 am

#90024

I have the following instead of insert trigger that I need a little help with. The reason I am using an instead of trigger is because I am inserting some columns of type nText. The problem is that I need to insert into an audit table the primary key field of the source table (projectId), which is an identity column. My question is "How can this be accomplished upon multiple inserts at once?" This is the trigger I currently have, which will work correctly only if one insert is performed. If multiple inserts are performed using the following code (i.e. the INSERTED table contains multiple rows), it duplicates the identity column in the audit table (of course). Footnote, cursors are a last resort. Thanks!
CREATE TRIGGER TR_PROJECT_INSERT ON tblProject
INSTEAD OF INSERT
AS
DECLARE @nextId int
Select @nextId = IDENT_CURRENT('tblproject')+IDENT_INCR('tblProject')
INSERT INTO TATMChangeHistory.dbo.tblProject
(ProjectId,
ProjectName,
ProjectNumber,
PENumber,
DateEntered,
LastUpdate,
UpdateBy,
ProjectDescrip,
ProjectObjectives,
TechChallenges,
TechApproach,
CapReqPayoff,
Review_PayOffPotential,
ActiveProject,
PocId,
SBS1Id,
SBS2Id,
SBS3Id,
TaskNumber,
WorkPackageNumber,
Review_CCTeam,
Review_Year,
FundingLeveNumberId,
CheckedOut,
ProjectTypeId,
PMOfficeId,
AuditType)
SELECT @nextId,
INSERTED.ProjectName,
INSERTED.ProjectNumber,
INSERTED.PENumber,
INSERTED.DateEntered,
INSERTED.LastUpdate,
INSERTED.UpdateBy, --Get from db Session,
INSERTED.ProjectDescrip,
INSERTED.ProjectObjectives,
INSERTED.TechChallenges,
INSERTED.TechApproach,
INSERTED.CapReqPayoff,
INSERTED.Review_PayOffPotential,
INSERTED.ActiveProject,
INSERTED.PocId,
INSERTED.SBS1Id,
INSERTED.SBS2Id,
INSERTED.SBS3Id,
INSERTED.TaskNumber,
INSERTED.WorkPackageNumber,
INSERTED.Review_CCTeam,
INSERTED.Review_Year,
INSERTED.FundingLeveNumberId,
INSERTED.CheckedOut,
INSERTED.ProjectTypeId,
INSERTED.PMOfficeId,
1
FROM INSERTED
--Proceed to insert the table with the new values
BEGIN
INSERT INTO TATM.dbo.tblProject
(ProjectName,
ProjectNumber,
PENumber,
DateEntered,
LastUpdate,
UpdateBy,
ProjectDescrip,
ProjectObjectives,
TechChallenges,
TechApproach,
CapReqPayoff,
Review_PayOffPotential,
ActiveProject,
PocId,
SBS1Id,
SBS2Id,
SBS3Id,
TaskNumber,
WorkPackageNumber,
Review_CCTeam,
Review_Year,
FundingLeveNumberId,
CheckedOut,
ProjectTypeId,
PMOfficeId)
SELECT INSERTED.ProjectName,
  INSERTED.ProjectNumber,
  INSERTED.PENumber,
  INSERTED.DateEntered,
  INSERTED.LastUpdate,
  INSERTED.UpdateBy,
  INSERTED.ProjectDescrip,
  INSERTED.ProjectObjectives,
  INSERTED.TechChallenges,
  INSERTED.TechApproach,
  INSERTED.CapReqPayoff,
  INSERTED.Review_PayOffPotential,
  INSERTED.ActiveProject,
  INSERTED.PocId,
  INSERTED.SBS1Id,
  INSERTED.SBS2Id,
  INSERTED.SBS3Id,
  INSERTED.TaskNumber,
  INSERTED.WorkPackageNumber,
  INSERTED.Review_CCTeam,
  INSERTED.Review_Year,
  INSERTED.FundingLeveNumberId,
  INSERTED.CheckedOut,
  INSERTED.ProjectTypeId,
  INSERTED.PMOfficeId
FROM INSERTED
END

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Create table #NewProject (

Id int IDENTITY(1,1),

....

)

declare @LastId int

INSERT INTO @NewProject (all columns but Id)

SELECT .....

FROM inserted

SELECT @LastID = MAX(ProjectId) from TATMChangeHistory.dbo.tblProject

SET IDENTITY_INSERT TATMChangeHistory.dbo.tblProject ON

INSERT INTO TATMChangeHistory.dbo.tblProject

(ProjectId, ....)

SELECT @LastId + Id, ....

FROM #NewProject

SET IDENTITY_INSERT TATMChangeHistory.dbo.tblProject OFF

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 2

in the above there are Two dangerous propositions:

1. SELECT @LastID = MAX(ProjectId) from TATMChangeHistory.dbo.tblProject

what would happen if a separate connection runs the same trigger before the first one is done inserting

2.SET IDENTITY_INSERT TATMChangeHistory.dbo.tblProject ON

Indentity insert can only be set on ONE table at a time

you should control those values in a separated table and access it through an sp with a code like:

sp_getNextId ( @TName varchar(20), @cnt int, @nextId int out)

Update IdTable Set @nextId = NextId = NextId+ @cnt where TableName =@TName

... you get the idea

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 3

1. Is not a problem. Just use

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

2. Can you write SQL statement to insert 10 rows from inserted using your way?

No cursors, please.

And if you don't need to match inserted rows within this trigger for another table insert, just use this:

INSERT INTO TATMChangeHistory.dbo.tblProject

(ProjectId, ....)

SELECT (select MAX(ProjectId) from TATMChangeHistory.dbo.tblProject) + Id, ....

FROM #NewProject

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 4

1. You are not serious are you

2. That will set a lock on that table for the duration of the insert statement

Of course you can call the procedure to obtain that 'next' value with no need for any locking mechanism (it is all implicit)

and then use the variable as you did on your select statement. All I am refering to is at the correct way to obtain that max value with minimal colisions and best performance

Cheers,

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 5

So, you end up with cursor.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 6

Cursor?

Create Trigger...

Declare @cnt int, @nextID int

Set @Cnt = @@rowcount

exec sp_GetNextID 'TableName', @cnt, @nextId out

-- Now use your same insert into...

* Noel

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

The solution may be to use an AFTER trigger and join to the underlying table.

Although a trigger cannot reference a LOB in the inserted tables, a trigger can reference a LOB in a base table.

Create table Foo

(fooId int not null identity(1,1)

,FooText text

, constraint Foo_PK primary key (fooId )

)

Create table Foo_Audit

(fooId int not null

,ActionTs datetime not null

,ActionDefn char(1) not null

-- I = Insert

-- U = Update

-- D = Delete

,FooText text

, constraint Foo_Audit_PK primary key (fooId, ActionTs )

)

go

create trigger Foo_ia_Audit

on Foo after insert

as

set nocount on set xact_abort on

if @@rowcount = 0 return

insert into Foo_Audit

(fooId ,ActionTs,ActionDefn ,FooText)

select Foo.fooId, current_timestamp, 'I', Foo.fooText

from Foo

join inserted

on Foo.FooId = inserted.FooId

go

SQL = Scarcely Qualifies as a Language

Sergiy SSC Guru Points: 110208 More actions · Answer 8

OK, what abot 10 rows to insert?

Can you show the INSERT statement?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

If to do it properly...

----------------------------

Declare @LastId int

Declare @Rowcnt int

Declare @ErrorNo int

SET @Rowcnt = 0

SET @ErrorNo = 0

INSERT INTO @NewProject (all columns but Id)

SELECT .....

FROM inserted

WHILE @Rowcnt = 0 AND @ErrorNo = 0

BEGIN

SELECT @LastID = MAX(ProjectId) from TATMChangeHistory.dbo.tblProject

SET IDENTITY_INSERT TATMChangeHistory.dbo.tblProject ON

INSERT INTO TATMChangeHistory.dbo.tblProject

(ProjectId, ....)

SELECT @LastId + Id, ....

FROM #NewProject

WHERE @LastId = (select MAX(ProjectId) from TATMChangeHistory.dbo.tblProject)

-- IF another process inserted something before this insert started no rows will be inserted

SELECT @Rowcnt = @@Rowcount, @ErrorNo = @@Error

-- IF no row inserted try again, unless there is an error

SET IDENTITY_INSERT TATMChangeHistory.dbo.tblProject OFF

END

------------------------

Thanks noeld for maling me think about this. I will definetely include this into my trigger tomorrow.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 10

Sergiy,

In my trigger code there is no need for a while loop because I am using an stored proc (have a look at it) which guards the last available Id to be used. in one statement therefore is transactionally secure.

The insert statement is almost exactly like yours:

INSERT INTO TATMChangeHistory.dbo.tblProject

(ProjectId, ....)

SELECT @LastId - Id, ....

FROM #NewProject

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 11

But what if @LastId become not really last Id before you start this statement?

If another process inserted 1 row in the table in the moment between retrieving of @LastId and starting of the INSERT?

That was you concern regarding my statement, wasn't it?

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 12

Sergy,

on my first post I said:

"... you should control those values in a separated table and access it through an sp .. "

Because that stored proc serializes the access and removes the need of any custom locking mechanism is that you get the Ids without problems

Cheers,

* Noel