Trigger Multiple Updates

Question

When a record is updated, a trigger occurs, where it assigns the new record, an important generated number (via stored procedure).

It works fine.

However, if you batch update records, the INSERTED table contains more than just the one record, which the trigger cannot deal with.

How can I loop or sql statement it, to apply a calculated value to each INSERTED (updated) row.

SELECT @id = id FROM INSERTED --  Could contain multiple rows, but (wrongly) only applies to one

IF (@id IS NOT NULL)
BEGIN
    DECLARE @No bigint
    EXEC assignNo @No = @No OUTPUT

    UPDATE myTable SET No = @No
    WHERE id = @id
END

Are you trying to put the same value of @No on all updated rows, or a separate value per row? — Joachim Isaksson, Jan 27 '14 at 16:44
@JoachimIsaksson Seperate value per row. As for the function idea, I will investigate. — IAmGroot, Jan 27 '14 at 16:49
@AaronBertrand Basically, inserts into a table, and takes the `SCOPE_IDENTITY()` to be used as a unique number throughout the entire database. So functions are not allowed, due to limitations of `INSERT`. Unless there is a better way entirely of managing a multi-thread assignment of unique IDs for use over ALL tables. — IAmGroot, Jan 27 '14 at 16:55
And this trigger is on `myTable`? Can it be made an `INSTEAD OF` trigger? Are there any other columns on the "other table" where you are generating these central numbers? If not, could a meaningless column be added? — Aaron Bertrand, Jan 27 '14 at 16:56
@AaronBertrand This trigger is yes. And similar triggers on all the other tables. I have never heard of an `INSTEAD OF` trigger before. I will investigate. — IAmGroot, Jan 27 '14 at 16:58
And if you insert 5 rows, does it really matter which row gets which scope_identity value? — Aaron Bertrand, Jan 27 '14 at 17:02
@AaronBertrand I dont believe that it will work. As this also applies to batch updates. Nor is it a primary key field. It is to keep everything in sync, by being a order number in the global syncing of data. No, it doesnt matter which one is which, as long as they all differ, and are sequential in a random order. aka, using 7-10, can be assigned, 8, 7, 10, 9. (but of course must be greater than anything done before the batch) — IAmGroot, Jan 27 '14 at 17:04
Why would you need to assign new sequential numbers to batch updates? And do you really not have a primary key on the target table to help identify rows? But why would that matter (or make "it" not work, not sure what "it" refers to)? I'm just trying to understand the requirements. Adding a useless nullable column to the table that serves as your central IDENTITY generator (or using a SEQUENCE instead, if 2012+) will help. — Aaron Bertrand, Jan 27 '14 at 17:10

score 4 · Accepted Answer · edited May 23 '17 at 10:25

Here is what I was trying to get at with my line of questioning. If your central IDENTITY generator has an additional, useless column, this can avoid inserting rows with DEFAULT VALUES, which is difficult, cumbersome and downright unintuitive to do with more than one row (and perhaps impossible on, say, SQL Server 2005). So, pretending this generator table looks like this:

CREATE TABLE dbo.OtherTable(OtherTableID INT IDENTITY(1,1), UselessColumn BIT);

And the real table you're inserting into looks like this:

CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1), [No] INT, foo VARCHAR(32));

We can create an INSTEAD OF INSERT trigger that inserts multiple rows into dbo.OtherTable, captures the set of IDENTITY values generated, then ultimately inserts those values along with the real data, assigning each generated value to a single row, arbitrarily.

CREATE TRIGGER dbo.trMyTable
ON dbo.MyTable
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @newIDs TABLE(ID INT IDENTITY(1,1), [No] INT);

  INSERT dbo.OtherTable(UselessColumn) 
  OUTPUT inserted.OtherTableID INTO @newIDs([No])
  SELECT NULL FROM inserted;

  INSERT dbo.MyTable([No],foo)
  SELECT n.[No], i.foo FROM @newIDs AS n 
  INNER JOIN 
  (
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY foo) FROM inserted
  ) AS i ON i.rn = n.ID;
END
GO

The reason an INSTEAD OF INSERT trigger is better is because it avoids a double operation (insert a bunch of rows, then update them all). Obviously you have more columns than foo; this is just as a simplistic demonstration.

If you also have to do something like this for batch updates, you're going to have to update the question with more requirements (and include information like what is the primary key on the target table).

This is a definite solution to my problem, Although as you mentioned, I need to do this for Update and Delete too. Although I should be able to figure that out now myself. My concern is that if something goes wrong, nothing will inserted. (Though i guess it should never go wrong). And the INSERT requires typing out all 30 field names. +1, thanks. will play around with it. — IAmGroot, Jan 28 '14 at 10:48
@Thanks for the answer. I made my own solution in the end. Posted for you to see. Regards. — IAmGroot, Jan 28 '14 at 14:15

score 0 · Answer 2 · answered Jan 28 '14 at 14:14

0

Here is the solution I went for.

I insert into my otherTable, using the IDs in INSERTED to generate Numbers. Plus I append a datetime string to make it more unique, as searching id alone, is not unique in this table.

I then update myTable using the IDs from INSERTED, and find the max No in otherTable using the unique id + datetime string identifier. This gets assigned to the correct record, and works in batch inserts/updates.

DECLARE @datetime as varchar(126)
SET @datetime = (SELECT CONVERT(VARCHAR, GETDATE(), 120))


INSERT INTO OtherTable       
SELECT I.id + ' ' + @datetime 
FROM INSERTED I

UPDATE myTable      
SET
    myTable.foo = 'U' , 
    myTable.No = (SELECT MAX(No) FROM OtherTable W WHERE W.Info = I.id+ ' ' + @datetime)
FROM INSERTED I
WHERE
    myTable.id = I.id

answered Jan 28 '14 at 14:14

IAmGroot

13,760
18
84
154

@AaronBertrand But it does work! :) And yes, inside a trigger. – IAmGroot Jan 28 '14 at 16:03
Well what does "work" really mean? Is Id really not unique enough that you need to append a string representation of the current time to it? And if you append the same time to all rows, how does that help? You seem to be doing a lot of stuff here that I just don't see the purpose of... but like I said, ok... – Aaron Bertrand Jan 28 '14 at 16:26
@AaronBertrand I was going to remove datetime, but only for the reason that I call `MAX()` so i would be getting the highest number anyway. If I update the same record, every time the same ID will be put into `OtherTable` so is never unique by itself. It assigns the incremental global numbers to any modified or inserted rows. – IAmGroot Jan 28 '14 at 19:00

Trigger Multiple Updates

2 Answers2