Menu
I'm trying to return the Id of a row I update in sql
UPDATE ITS2_UserNames SET AupIp = @AupIp WHERE @Customer_ID = TCID AND @Handle_ID = ID SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID and I keep getting Null for the ID, how can I get this?
218
The updated record is available in the INSERTED table. The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the Column name of the Table to it.
If employee_id is an IDENTITY column and you are sure that rows aren't manually inserted out of order, you should be able to use this variation of your query to select the data in sequence, newest first: SELECT ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, EMPLOYEE_ID, EMPLOYEE_NAME FROM dbo.
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
The @@identity and scope_identity() will hand you the identity of a new row, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If it is a different field, you should use the OUTPUT clause to return the ID of the updated row:
UPDATE ITS2_UserNames SET AupIp = @AupIp OUTPUT INSERTED.PrimaryKeyID WHERE @Customer_ID = TCID AND @Handle_ID = ID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
