none
Identity, output and instead of triggers

    Вопрос

  • Hello!

    I have found many resources on this topic, but none of them *quite* answer what I need to ask.  Here is my scenario.

    I have a stored procedure that inserts a single item into a table "MainTable".  Once that is done, the procedure needs to return the new identity to the client application: select scope_identity().

    MainTable has an insert trigger that looks very similar to this:

    insert into MainTable (A, B)
    select A, B from inserted
    
    declare @NewID int
    select @NewID = scope_identity()
    
    insert into ExtraTable1 (A)
    values (@NewID)
    
    insert into ExtraTable2 (A)
    values (@NewID)

    Now the call to scope_identity() in the stored procedure returns null, because the insert is done out of its scope. I can't use @@Identity as the last inserted table was ExtraTable2. I found this lovely article about using the output keyword:

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Scrolling down to H is *almost* what I need.  I have editted my insert statement in the trigger to look like this:

    insert into MainTable (A, B)
    output inserted.ID
    select A, B from inserted

    But this gives me an error:

    The target table 'MainTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

    Well... I'm trying to use that statement inside the trigger and the article above says that is okay.  I do, however, also have an update trigger on the same table - though I think it is unlikely to be referring to that.

    I have also tried a different approach which is to just select the correct ID inside the trigger.  I read elsewhere that this works - well it doesn't for me.  Result sets are allowed to be returned from my SQL Server instance.  I am in dire need of help with this, I think I have exhausted my ability to research the issue.  Thank you very much for any useful assistance provided.

    8 июня 2012 г. 16:30

Ответы

  • The answer is simple.  You cannot have an INSTEAD OF trigger running parallel to a AFTER INSERT trigger and expect everything to work fine.

    In an INSTEAD OF INSERT trigger you are able to choose what value you return as the ID by using OUTPUT or be just selecting the value.  You do not need to select the value at the end of the trigger as far as I can tell either.  This allows you to have a trigger that inserts to multiple tables and still return the identifier you wish for when you call scope_identity().  This is not limitted to stored procedures, you can test this in the query analyser.

    The reason I had a problem is because I also had a FOR INSERT, UPDATE trigger on the same table, performing a different task.  Trying to save the INSTEAD OF trigger causes an error (stated above).  You can save the triggers in a different order without error, but the same error will appear if you run an insert anyway (no work is done).  Whatever you have in your FOR INSERT trigger will need to be moved into the INSTEAD OF insert trigger if you must get that value back.

    • Помечено в качестве ответа Tim Charters 11 июня 2012 г. 11:04
    11 июня 2012 г. 11:04

Все ответы

  • Hi,

    One of ways of achieving what you are trying, is to OUTPUT into a table variable and then select from that table variable.

    DECLARE @MyTableVar TABLE
    ( A INT,
    B INT)
    
    insert into MainTable (A, B)
    output inserted.* INTO @MyTableVar 
    select A, B from @MyTableVar 

    My main concern is that why do you have Trigger on the main table to perform inserts into the extra table. Since you have a stored proc to INSERT into the main table, why not insert into the extra table in that proc itself?

    something like;

    CREATE PROC my_proc <input parameters>
    BEGIN
    
    -- BEGIN TRANSACTION
    
    -- INSERT INTO MAIN TABLE
    
    -- retrieve scope identity
    
    -- insert into extra table 1
    
    -- insert into extra table 2
    
    -- check errors after each statement
    
    -- COMMIT if no error
    
    -- rollback if errors
    
    
    END

    Hope this helps!


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Предложено в качестве ответа Naomi NModerator 8 июня 2012 г. 16:47
    • Отменено предложение в качестве ответа Tim Charters 8 июня 2012 г. 17:03
    8 июня 2012 г. 16:46
  • Keeping it in the trigger makes gosh golly darned sure that the other tables are updated, whether it was done through the main user client, admin client, phone apps, phone site, facebook app or manually using SQL Management studio.

    There are cases where MainTable may be inserted into from different places.  A potential solution to this problem would be to deny insert rights to the sql account, forcing the procedure to always be used, but I elected for triggers to keep my procedure count lower.  Rebuilding that at this stage would be... a catastrophe.  Besides I like this way, it will help me with something cunning later on.

    I will try what you've suggested here now...

    8 июня 2012 г. 16:54
  • Unfortunately no success.  All we have done here is change where we were getting the identity from.  I can get the identity in the trigger, no problem.  But selecting it at the end and then passing it back to the stored procedure is the issue.  This is supposed to be possible, so there is something else blocking it.  What should I be looking for?
    8 июня 2012 г. 17:06
  • If I understand the question correctly, here it should be the solution

    create table MainTable(id int identity(1,1), a int, b int)
    go
    create table ExtraTable1(id int)
    go
    create trigger t_MainTable on MainTable
    instead of insert
    as
    begin
    	insert into MainTable(a,b)
    		select a,b from inserted
    	declare @newid int
    	select @newid = scope_identity()
    	insert into ExtraTable1 values(@newid)
    	set context_info @newid
    end
    go
    insert into MainTable values(1,2)
    select cast(substring(CONTEXT_INFO(),1,4) as int)
    select * from ExtraTable1


    Regards

    John Huang, MVP-SQL, MCM-SQL, http://www.sqlnotes.info

    8 июня 2012 г. 17:51
  • In an ideal database design, you would have a natural key in additional to the identity value (surrogate key).  For example, in a table that stored buildings a natural key might be the address.  Now an address is very long, so you might want to have an Identity value to use as the primary key, so that you only needed to store the short integer ID value in any child tables.  But you would also have a unique key on the address columns to insure that no two buildings had the same address.  If you have this sort of case, then just have you instead of insert trigger insert the row.  Then the calling code could use the natural key to select the identity value that was assigned when the trigger inserted the actual row.

    Sadly, lots of databases are designed with an identity value as the only candidate key.  Not the best practice, but it often happens and when you are working with an existing database design, it may not be practical to go back an add a natural key.  Although if you can, this will usually be the best case.

    But if you can't do that, there is no natural way for triggers to pass back values directly (unlike, for example, stored procedures that have output parameters).  One way to do it is to define a temp table in the outer code, then do the insert that fires the trigger.  The trigger can see temp tables that were created in the outer code, so the trigger can save the identity value in the temp table and the outer code can then get that value.  Of course, if the outer doesn't create the temp table, if you don't want the insert to fail, you must check in the trigger if the temp table exists and only attempt to insert into it if the table exists.  Sample code:

    -- Create Table
    Create Table Foo(ID int identity, A int, B int);
    go
    -- Create Trigger that optionally saves identity in temp table
    Create Trigger FooTr On Foo Instead Of Insert As
    Begin
      Insert Foo(A, B)
      Select A, B From inserted;
      -- If code that fired trigger has created temp table
      -- save new identity value in that table
      If Object_ID('tempdb..#IdentityValues') Is Not Null
        Insert #IdentityValues(IdentityValue) Values(SCOPE_IDENTITY());
    End
    go
    -- Insert a row without temp table
    -- insert works, but, of course, you don't get the identity value back
    Insert Foo(A, B) Values (7,2);
    Select * From Foo;
    
    -- Now to insert row and get inserted identity value back
    -- create temp table used in trigger, insert the row, then use
    -- temp table to retrieve identity value
    -- then drop temp table
    Create Table #IdentityValues(IdentityValue int);
    Declare @NewID int;
    Insert Foo(A, B) Values (17,32);
    Select * From Foo;
    Select @NewID=IdentityValue From #IdentityValues;
    Drop Table #IdentityValues;
    -- Check to see that we got the new identity value
    Select @NewID As NewID;
    go
    -- Cleanup
    Drop Trigger FooTr;
    go
    Drop Table Foo;

    Tom

    8 июня 2012 г. 18:09
  • Ok ! So you are trying to solve the problems of poor design and insufficient access control with an elaborate code based solution. Yes, that sure makes a gosh golly darned good case for using triggers. As Tom mentioned , you are limited by the fact that an you possibly don't have a natural key and the fact that you cannot pass values out of a trigger like you would for a proc. I would support Tom's suggestion f using a temp table to stage this data ( either local - be aware of the scope of a local temp table - or global), or you could possibly create a permanent work table and truncate it at the beginning to the trigger before populating it with the result or scope_identity and then use this row in subsequent inserts of extra tables. Good Luck !

    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    9 июня 2012 г. 14:53
  • @John and @Tom - I am not in the office so I cannot try your solutions on the real database until tomorrow.  Although Tom your solution is more a workaround than actually tackling the problem.

    @Sanil, I don't think that attitude is productive or deserved.  I don't see anything wrong with using triggers to enforce data integrity.  In fact, that is their primary purpose.

    Also guys: "you cannot pass values out of a trigger like you would for a proc".  That is not what I am asking.  I have set up a test to show you the problem because I think you are still misunderstanding.  Please follow these steps (i'm using VS 2010 server explorer for all of this, but I think you can translate into your preferred tool):

    1. Get into / create your test catalog for doing this.
    2. Create table "Main" (MainID PK autoindexing from 1, Value varchar)
    3. Create table "First" (FirstID PK autoindexing from 1000, MainID int)
    4. Create table "Second" (SecondID PK autoindexing from 100000, MainID int)
    5. Write a query: "insert into Main (value) values ('test') select scope_identity()"
    6. If you run this, you should get "1" as a result.
    7. Create trigger on Main instead of insert: "insert into Main(value) select value from inserted"
    8. Run the query in 5: your result should be NULL
    9. Edit the trigger so it reads: "insert into Main (value) output inserted.MainID select value from inserted
    10. Run query in 5 and you should get the correct result (3).
    11. Edit the trigger again so it looks like this: insert into Main (value) select value from inserted  declare @MainID int select @MainID = scope_identity()  insert into First (MainID) values (@MainID)  select @MainID
    12. Run the query in 5 and you still get the correct result from the scope_identity().

    So in conclusion what I am doing SHOULD work as you CAN pass a value back from the trigger for scope_identity() to grab hold of. My demo proves it. However something else in my production database is causing me issues and I do not know what.  This isn't elaborate code, this is documented in the link I pasted above.  I will get back to you as soon as I can on this.  Thanks again!

    10 июня 2012 г. 9:29
  • Sanil's comment may have been a bit sarcastic, but there is still something in your design that I don't understand. Or more precisely, something that does not seem like good software design to me.

    You say you want a trigger, because you don't know where the insert operation will come from; it may not come from your stored procedure. I don't question this. When some people advocate stored procedures over triggers, this is exactly my argument.

    What I find problematic is that you for some reason want to know in the stored procedure what happened in the trigger. A stored procedure should not know about the trigger, just like class high up in the inheritance hierachy should not know about overrides on a lower level.

    Moreover, if your trigger performs actions for the benefit for this particular stored procedure, what happens when the INSERT comes from elsewhere? Will the same logic be respected in this case? Apparently the stored procedure is doing something more than just the INSERT, else you would not need to know the values of the inserted rows.

    Do you need to have an INSTEAD OF triggers? The primary reason INSTEAD OF triggers were introduced were to permit updates through views that are not updatable directly through their definition. I have found INSTEAD OF triggers to be of somewhat dubious value. Since you need to repeat the operation that fired the trigger, there is a maintenance problem. I have mainly used them for situations when I wanted to outlaw an operation entierly (and thus there is no need for repeat).

    I don't know what you are doing in the stored procedure, but with an AFTER trigger you could easily pick up the IDENTITY values in the procedure.

    There is also reason to consider whether you really need to use IDENTITY. As Tom pointed out, natural keys is much to prefer. Indeed, they are not always practically possible. But I get an uneasy feeling when I see that every table has a surrogate key. Shouldn't the keys from MainTable be part of the key in ExtraTable?

    And even if you think that surrogate keys is the only way to go, you don't have to do them with IDENTITY; you might as well roll your own. (And if you are on SQL 2012, you should absolutely use sequences instead.) The main advantage of IDENTITY over rolling your own is that the latter will require a serialisation point which will reduce throughput in a high-transaction environment.

    Finally, it can be worth pointing out that Microsoft has deprecated returning results sets from triggers, and there is a configuration option on server level to stop this from happening in the first place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    10 июня 2012 г. 10:00
  • I found the solution, which I will state in the next post, so the topic is easy to follow.

    Hello Erland, thanks for the comment - I will answer the questions per paragraph.

    The trigger does do some data checking as well.  In this case the Main table is a table of bids (as in an auction).  A bid has a status to determine it's state, however only some state changes are allowed (once a bid has been cancelled it can not be accepted by the auctioner, so i disallow that change with raiserror).  I don't think there is anything wrong with this in design, is there?  Please do correct me f I'm wrong as I'm happy to improve design where I can.

    The reason I want to know the ID in the stored procedure is simply so I can return it to the client application and avoid another round trip.  Imagine a list of bids on a website.  All data is entered on the website, sent to the web server, saved in database.  In that application I would want to add the new bid to the list.  I would need an identifier for the bid in that list so that I can run any updates on the next post from the client.  This is where you tell me I should have another identifier - I suppose I already do - the bidder ID and the item ID being bid on (I have a rule only 1 bid per user per item) and yes I could use that. However, there is a similar situation with the items themselves. 2 items can look utterly identical and still be valid as two separate items. I would have to generate another key, at which point I would be in a very similar situation, unless I generate the key in the client application, which feels weird. If I do not return the key, I would need to refetch the list (or the whole item) based on this extra key and return that. I don't think I'm being crazy by preferring to return a single integer instead, especially when I'm not breaking the system to make it (as you will see in a moment).

    I can plausibly use FOR INSERT triggers, but they run on batch and much of what I do is for single records only.  In fact, batch inserts of Bids should never happen unless I'm doing a manual database migration or similar.  It seemed like a more logical and efficient place to put my data checks.  I do not understand what you mean here: "Since you need to repeat the operation that fired the trigger, there is a maintenance problem."  It is true you do need to remember that you have a trigger that does the insert - meaning if you add a field you want to insert into you do need to update the trigger too, but this is a single extra task and is very easy.  Much easier than having to go through any and every stored procedure you have that inserts into that table to update any rules you have (along with error codes etc etc).

    The value returned isn't for this stored procedure, it is just returned for scope_identity() calls to catch - so it is valid for any procedure or query. Nice.

    I think I need to re-read some modern database design resource.  I am clearly a bit out of date now.

    11 июня 2012 г. 10:55
  • The answer is simple.  You cannot have an INSTEAD OF trigger running parallel to a AFTER INSERT trigger and expect everything to work fine.

    In an INSTEAD OF INSERT trigger you are able to choose what value you return as the ID by using OUTPUT or be just selecting the value.  You do not need to select the value at the end of the trigger as far as I can tell either.  This allows you to have a trigger that inserts to multiple tables and still return the identifier you wish for when you call scope_identity().  This is not limitted to stored procedures, you can test this in the query analyser.

    The reason I had a problem is because I also had a FOR INSERT, UPDATE trigger on the same table, performing a different task.  Trying to save the INSTEAD OF trigger causes an error (stated above).  You can save the triggers in a different order without error, but the same error will appear if you run an insert anyway (no work is done).  Whatever you have in your FOR INSERT trigger will need to be moved into the INSTEAD OF insert trigger if you must get that value back.

    • Помечено в качестве ответа Tim Charters 11 июня 2012 г. 11:04
    11 июня 2012 г. 11:04
  • I do not understand what you mean here: "Since you need to repeat the operation that fired the trigger, there is a maintenance problem."  It is true you do need to remember that you have a trigger that does the insert - meaning if you add a field you want to insert into you do need to update the trigger too, but this is a single extra task and is very easy.  Much easier than having to go through any and every stored procedure you have that inserts into that table to update any rules you have (along with error codes etc etc).

    Still, compared to an AFTER INSERT trigger that's one more piece of code that has to be changed. And there is the risk that someone adds a new column, adds it to the INSERT list where it's needed, but can't understand why the column makes it to the table, because the person is not aware of the INSTEAD OF trigger.

    I can plausibly use FOR INSERT triggers, but they run on batch

    There is no difference between AFTER triggers and INSTEAD OF triggers in this regard.

    And all you tell me about the setup, makes me think that you should have all the code in AFTER triggers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    11 июня 2012 г. 22:24