Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I inserted on tables that exists instead of triggers on those. @@Identity , IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get last identity of inserted row

share|improve this question
    
There is inserted but no row inserted when an INSTEAD OF trigger triggers. –  ypercube Jun 6 '12 at 7:19
    
Check this SO question: it may help. stackoverflow.com/q/908257/27535 –  gbn Jun 6 '12 at 8:58
add comment

2 Answers 2

up vote 8 down vote accepted

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?

share|improve this answer
    
I want to control data integrity of inserted row. before save them. If data not good, I raise error proportional message. –  mehdi lotfi Jun 6 '12 at 7:19
2  
You are describing a foreign key. It should be the application responsibility to insert in the child table, not a trigger. Doing it from a trigger is bad design, and in anyway it can be done from a normal AFTER trigger. An after trigger can raise errors and cause rollback, which is the better option than an instead-of trigger. –  Remus Rusanu Jun 6 '12 at 7:36
1  
What an absurd notion - "instead of triggers are a serious code smell"? They are very useful compared to an after trigger - where, if your business rules are violated, you've done the work twice - you've inserted the rows and then you've rolled them back. An instead of trigger can prevent any of the work from happening if your business rules can't be enforced with normal DRI or other constraints. –  Aaron Bertrand Jun 6 '12 at 12:09
1  
While "instead of triggers are a serious code smell", is not a strict rule, it is based on actual possible problems it can cause in a complete system. In general, business rule breakers should never happen in the system, let alone reaching to database level. In that case, a rule validation in the trigger is only appropriate as a last defense mechanism in case there is a hole in the system. –  Alireza Jun 7 '12 at 6:55
3  
Declarative integrity is always better than a trigger. An after trigger is always better than an instead of trigger. Instead-of triggers have 'funky' behavior in a lot of situations, they are opaque to access path optimizations in DML, they make isolation levels behave erratic. Instead-of triggers scream 'I should had been an access stored procedure instead'. And I don't buy the 'do the work twice' argument at all, optimizing the exception path should not influence the design, specially at the cost of the slowing the frequent path. –  Remus Rusanu Jun 7 '12 at 7:32
show 7 more comments

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

Results:

id
----
1

Now clean up:

DROP TABLE dbo.SmellThis;

As an aside, you should never, ever, ever be using @@IDENTITY or 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 VALUES(),(),() or INSERT...SELECT - which SCOPE_IDENTITY would you be setting to your variable?

share|improve this answer
    
How I save result of inserted record in variable table for use later. –  mehdi lotfi Jun 6 '12 at 12:19
    
@mehdi can you define "later"? And can't you add columns to the table variable I've already declared above? –  Aaron Bertrand Jun 6 '12 at 12:20
    
I should be more review. –  mehdi lotfi Jun 6 '12 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.