When I insert into tables using instead of triggers,
SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?
With an INSTEAD_OF trigger it means no insert occurred yet. You cannot know the identity since it wasn't yet generated. Sneaking the value from metadata is possible (
DBCC CHECKIDENT) but relying on it will not work correctly under concurrency and besides it requires elevated privileges.
INSTEAD_OF triggers are extremely seldom required and a serious code smell. Are you sure you need it? Can't you do the work with a regular AFTER trigger?
In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert.
USE tempdb; GO CREATE TABLE dbo.SmellThis ( id INT IDENTITY(1,1), name VARCHAR(32) ); GO CREATE TRIGGER dbo.SmellThis_First ON dbo.SmellThis INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @ids TABLE(id INT); IF NOT EXISTS ( SELECT 1 FROM sys.objects AS o INNER JOIN inserted AS i ON o.name = i.name ) INSERT dbo.SmellThis(name) OUTPUT inserted.id INTO @ids SELECT name FROM inserted; SELECT id FROM @ids; END GO INSERT dbo.SmellThis(name) SELECT 'Remus'; GO
id ---- 1
Now clean up:
DROP TABLE dbo.SmellThis;
As an aside, you should never, ever, ever be using
IDENT_CURRENT() anyway. And
SCOPE_IDENTITY should be reserved for situations where you know only one row can ever be inserted. A common misconception with triggers is that they fire per row, like in other platforms, but in SQL Server they fire per operation - so a multi-row insert using
INSERT...SELECT - which
SCOPE_IDENTITY would you be setting to your variable?
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>