Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-04-18 : 18:21:19
|
Paul writes "Hi,I have a problem with an application that inserts a new record into a sql server 2000 table. It is a windows app that I cannot customize at all. When it inserts a record, I need to run a trigger that grabs the last inserted ID and then can use that ID within the trigger. Basically the trigger inserts that ID into another table, etc. I built the trigger, but it does not grab that last inserted ID of the insert done outside of the trigger. I know I am missing something but am stuck. Here is the code for the trigger: CREATE TRIGGER [tr_cardnum_insert] ON [dbo].[Person_Image] AFTER INSERT AS Select @@IDENTITY As 'newPassID' Declare @newPassID As int Select CurrentCardType As cardtype From Person_Image Where PassHolder_num = @newPassID Declare @cardtype As char(1) SET NOCOUNT ON Insert into CardsIssued values(@cardtype, @newPassID, GetDate()) Select newCardnum = @@IDENTITY Declare @newCardnum As int Update Person_Image Set CurrentCardNUm = @newCardnum SET NOCOUNT OFF Any help would be greatly appreciated. Not even sure if it is possible for the trigger to obtain the last auto-generated ID outside of the trigger, even though it is on that same table.Platform: SQL Server 2000 EnterpriseWindows 2003 Standard edition" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-18 : 18:27:49
|
| The value of the identity will be in the inserted table.select @cardtype = cardtype, @newPassID = PassHolder_num from inserted.your code should be:Declare @cardtype As char(1) Declare @newPassID As int Select @newPassID = @@IDENTITY Select @cardtype = CurrentCardType From Person_Image Where PassHolder_num = @newPassID Declare @newCardnum As int Insert into CardsIssued values(@cardtype, @newPassID, GetDate()) Select @newCardnum = @@IDENTITY Update Person_Image Set CurrentCardNUm = @newCardnum The last statement will update all rows in Person_Image which is probably not what you want.Note that this will upset the application if it uses @@identity rather than scope_identity (or vice versa depending on what you are trying to do).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Complex Trigger / @@Identity problem