how to retrieve an id from an insert command ? 2

[nimapoggy]

Hi !
I created a procedure that insert datas into a table.
The id of the new inserted row is generated by sqlserver as an auto-increment integer.

But is it possible that this procedure returns the id it created during this insert ?

Infact I would like to store this id and the date and time of the insert in another table wich would be an history of the insert on the first table.

 

[clapag22]

Read up on INSERT TRIGGERS in BOL. They can accomplish exactly what you need to do.

 

[bperry]

In your procedure, you can do something like this.
--------------
declare @newId int
Insert into Table1

select @newId = @@Identity
insert into HistTable values (@newId, getdate())
-----------------
This is an example, and you can adjust the names, columns, etc to suit your situation.

As i have shown yuo, we usually like to trap the @@Identity into a local variable first, but actually, this would have worked as well:

insert into HistTable values (@@Identity, getdate())

 

[nimapoggy]

cool, it works well.
Now, another step in the problem (i'm newbee, so there's a lot of problems

So, I created an insert trigger on my user table that call a procedure on the history table to insert a row in this history table.

As shown on another subject about triggers, i use the "inserted.USER_ID" field in the trigger as param of the procedure call.

But I also would like to store in the history table the id of the user that made this insert. I guess I can't use params on triggers and this ID is not available from the "inserted" table.

Resume of the process :

1 - Call procedure to insert datas in user table.
Params are name, password, mail...

2 - As the insert is done, a trigger runs on the user table

3 - It call a procedure from the history table
this porcedure use params as id of the inserted user, insertion date (getdate function), and the id of the user that made the insert.

But this id is only known when the first procedure is called and I have to use it in the history procedure...
Here is the problem. How to use an external param in a trigger? is it good to use trigger for this problem ?
Is it the write use of procedure and triggers ?

 

[nimapoggy]

OK, forget the trigger (now I understand how it works, cool...)
It well work with @@Identity in the user insert procedure.
Thanks to all of you !!!