CODEWITHSUNDEEP
sqlsql-servertriggers
IAmGroot
IAmGroot

Reputation: 13855

Trigger Multiple Updates

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

Upvotes: 0

Views: 3199

Answers (2)

IAmGroot
IAmGroot

Reputation: 13855

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

Upvotes: 0

anon
anon

Reputation:

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).

Upvotes: 4

Related Questions