Instead of trigger in SQL Server loses SCOPE_IDENTITY?

Since you're on SQL 2008, I would highly recommend using the OUTPUT clause instead of one of the custom identity functions. SCOPE_IDENTITY currently has some issues with parallel queries that cause me to recommend against it entirely. @@Identity does not, but it's still not as explicit, and as flexible, as OUTPUT. Plus OUTPUT handles multi-row inserts. Have a look at the BOL article which has some great examples.

I was having serious reservations about using @@identity, because it can return the wrong answer.

But there is a workaround to force @@identity to have the scope_identity() value.

Just for completeness, first I'll list a couple of other workarounds for this problem I've seen on the web:

1. Make the trigger return a rowset. Then, in a wrapper SP that performs the insert, do INSERT Table1 EXEC sp_ExecuteSQL ... to yet another table. Then scope_identity() will work. This is messy because it requires dynamic SQL which is a pain. Also, be aware that dynamic SQL runs under the permissions of the user calling the SP rather than the permissions of the owner of the SP. If the original client could insert to the table, he should still have that permission, just know that you could run into problems if you deny permission to insert directly to the table.

2. If there is another candidate key, get the identity of the inserted row(s) using those keys. For example, if Name has a unique index on it, then you can insert, then select the (max for multiple rows) ID from the table you just inserted to using Name. While this may have concurrency problems if another session deletes the row you just inserted, it's no worse than in the original situation if someone deleted your row before the application could use it.

Now, here's how to definitively make your trigger safe for @@Identity to return the correct value, even if your SP or another trigger inserts to an identity-bearing table after the main insert.

Also, please put comments in your code about what you are doing and why so that future visitors to the trigger don't break things or waste time trying to figure it out.

CREATE TRIGGER TR_MyTable_I ON MyTable INSTEAD OF INSERT AS SET NOCOUNT ON DECLARE @MyTableID int INSERT MyTable (Name, SystemUser) SELECT I.Name, System_User FROM Inserted SET @MyTableID = Scope_Identity() INSERT AuditTable (SystemUser, Notes) SELECT SystemUser, 'Added Name ' + I.Name FROM Inserted -- The following statement MUST be last in this trigger. It resets @@Identity -- to be the same as the earlier Scope_Identity() value. SELECT MyTableID INTO #Trash FROM MyTable WHERE MyTableID = @MyTableID 

Normally, the extra insert to the audit table would break everything, because since it has an identity column, then @@Identity will return that value instead of the one from the insertion to MyTable. However, the final select creates a new @@Identity value that is the correct one, based on the Scope_Identity() that we saved from earlier. This also proofs it against any possible additional AFTER trigger on the MyTable table.

Update:

I just noticed that an INSTEAD OF trigger isn't necessary here. This does everything you were looking for:

CREATE TRIGGER dbo.TR_Payments_Insert ON dbo.Payment FOR INSERT AS SET NOCOUNT ON; IF EXISTS ( SELECT * FROM Inserted I INNER JOIN dbo.Payment P ON I.CustomerID = P.CustomerID WHERE I.DateFrom < P.DateTo AND P.DateFrom < I.DateTo ) ROLLBACK TRAN; 

This of course allows scope_identity() to keep working. The only drawback is that a rolled-back insert on an identity table does consume the identity values used (the identity value is still incremented by the number of rows in the insert attempt).

I've been staring at this for a few minutes and don't have absolute certainty right now, but I think this preserves the meaning of an inclusive start time and an exclusive end time. If the end time was inclusive (which would be odd to me) then the comparisons would need to use <= instead of <.

Main Problem : Trigger and Entity framework both work in diffrent scope. The problem is, that if you generate new PK value in trigger, it is different scope. Thus this command returns zero rows and EF will throw exception.

The solution is to add the following SELECT statement at the end of your Trigger:

SELECT * FROM deleted UNION ALL SELECT * FROM inserted; 

in place of * you can mention all the column name including

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname> 

Like araqnid commented, the trigger seems to rollback the transaction when a condition is met. You can do that easier with an AFTER INSTERT trigger:

CREATE TRIGGER [dbo].[TR_Payments_Insert] ON [dbo].[Payment] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF <Condition> BEGIN ROLLBACK TRANSACTION END END 

Then you can use SCOPE_IDENTITY() again, because the INSERT is no longer done in the trigger.

The condition itself seems to let two identical rows past, if they're in the same insert. With the AFTER INSERT trigger, you can rewrite the condition like:

IF EXISTS( SELECT * FROM dbo.Payment a LEFT JOIN dbo.Payment b ON a.Id <> b.Id AND a.CustomerId = b.CustomerId AND (a.DateFrom BETWEEN b.DateFrom AND b.DateTo OR a.DateTo BETWEEN b.DateFrom AND b.DateTo) WHERE b.Id is NOT NULL) 

And it will catch duplicate rows, because now it can differentiate them based on Id. It also works if you delete a row and replace it with another row in the same statement.

Anyway, if you want my advice, move away from triggers altogether. As you can see even for this example they are very complex. Do the insert through a stored procedure. They are simpler and faster than triggers:

create procedure dbo.InsertPayment @DateFrom datetime, @DateTo datetime, @CustomerId int, @AdminId int as BEGIN TRANSACTION IF NOT EXISTS ( SELECT * FROM dbo.Payment WHERE CustomerId = @CustomerId AND (@DateFrom BETWEEN DateFrom AND DateTo OR @DateTo BETWEEN DateFrom AND DateTo)) BEGIN INSERT into dbo.Payment (DateFrom, DateTo, CustomerId, AdminId) VALUES (@DateFrom, @DateTo, @CustomerId, @AdminId) END COMMIT TRANSACTION 

A little late to the party, but I was looking into this issue myself. A workaround is to create a temp table in the calling procedure where the insert is being performed, insert the scope identity into that temp table from inside the instead of trigger, and then read the identity value out of the temp table once the insertion is complete.

In procedure:

CREATE table #temp ( id int ) ... insert statement ... select id from #temp -- (you can add sorting and top 1 selection for extra safety) drop table #temp 

In instead of trigger:

-- this check covers you for any inserts that don't want an identity value returned (and therefore don't provide a temp table) IF OBJECT_ID('tempdb..#temp') is not null begin insert into #temp(id) values (SCOPE_IDENTITY()) end 

You probably want to call it something other than #temp for safety sake (something long and random enough that no one else would be using it: #temp1234235234563785635).