Multi-row action for Instead of trigger SQL Server

Question

Scenario/background:

I am trying to create a table of "Tests". For purposes of this question my table will only have 5 columns defined as follows:

 CREATE TABLE TestTable
 (
      Id UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,
      Name VARCHAR(75) NOT NULL,
      DateRequested DATETIME NOT NULL DEFAULT GETDATE(),
      TestYear AS YEAR(DateRequested) PERSISTED NOT NULL, -- computed column that shows the year the test was requested. I want to persist this column so I can index on it if need be.
      TestNumber CHAR(4) NOT NULL DEFAULT '0000', -- need this to auto increment but also needs to reset for the first test of the year.
      CONSTRAINT TestTablePK PRIMARY KEY(Id)
 );
 GO

My requirement is that I want the 'TestNumber' to "auto-increment" based on the year. For example:

 GUID, Test 1 in Old Yr, 2013-01-01 05:00:00.000, 2013, 0001
 GUID, Test 2 in Old Yr, 2013-12-25 11:00:00.000, 2013, 0002
 GUID, Test 3 in Old Yr, 2013-12-26 09:00:00.000, 2013, 0003
 ...., ................, ......................., ...., N
 GUID, Test N in Old Yr, 2013-12-31 09:00:00.000, 2013, N+1
 GUID, Test 1 in New Yr, 2014-01-01 11:00:00.000, 2014, 0001   <-- reset to 1

I was thinking that it would be an auto-increment column but how would I reset it based on this being the first test of a new year? So my incorrect solution thus far has been an "instead of insert" trigger defined as follows:

 CREATE TRIGGER InsteadOfInsertTrigger ON dbo.TestTable
 INSTEAD OF INSERT AS
 BEGIN
      -- Get the year of the test request being inserted from the pseudo-insert table.
      DECLARE @TestYear INT;
      SET @TestYear = (SELECT YEAR(DateRequested) FROM inserted);

      -- Grab the maximum TestNumber from TestTable based on the year
      -- that we are inserting a record for.
      DECLARE @MaxTestNumber INT;
      SET @MaxTestNumber = (SELECT MAX(TestNumber) FROM dbo.TestTable WHERE TestYear = @TestYear);

      -- If this is the first test of the year being inserted it is a special case
      IF @MaxTestNumber IS NULL
      BEGIN
           SET @MaxTestNumber = 0;
      END;

      -- Here we take the MaxTestNumber, add 1 to it, and then pad it with 
      -- the appropriate number of zero's in front of it 
      DECLARE @TestNumber VARCHAR(4);
      SET @TestNumber = (SELECT RIGHT('0000' + CAST((@MaxTestNumber + 1) AS VARCHAR(4)), 4));

      INSERT INTO dbo.TestTable(Name, DateRequested, TestNumber)
      SELECT Name, DateRequested, @TestNumber FROM inserted;
 
 END;
 GO

Now here is some DML showing the trigger in action:

 INSERT INTO TestTable(Name, DateRequested)
 VALUES('Some Test', '05-05-2013');
 INSERT INTO TestTable(Name, DateRequested)
 VALUES('Some Other Test', '12-25-2013');
 INSERT INTO TestTable(Name, DateRequested)
 VALUES('Blah Blah', '12-31-2013');
 INSERT INTO TestTable(Name, DateRequested)
 VALUES('Foo', '01-01-2014');
 SELECT * FROM TestTable ORDER BY TestYear ASC, TestNumber ASC;

So as you can see my trigger works for single row inserts but a keen eye will be able to tell it will not work for multi-row inserts.

 CREATE TABLE TempTestTable
 (
      Name VARCHAR(75) NOT NULL,
      DateRequested DATETIME NOT NULL DEFAULT GETDATE()
 );
 GO

 INSERT INTO TempTestTable(Name, DateRequested)
 VALUES('Test1', '01-01-2012');
 INSERT INTO TempTestTable(Name, DateRequested)
 VALUES('Test2', '12-25-2012');
 INSERT INTO TempTestTable(Name, DateRequested)
 VALUES('Test3', '01-01-2013');
 INSERT INTO TempTestTable(Name, DateRequested)
 VALUES('Test4', '01-01-2014');

 -- This doesnt work because it is a multi-row insert.
 INSERT INTO TestTable(Name, DateRequested)
 SELECT Name, DateRequested FROM TempTestTable;

My Question

I realize that I can probably handle this with stored procedures and force users to use stored procedures when updating the tables but I want to be extra careful and prevent sysadmins from being able to do direct inserts to the table with an incorrect 'TestNumber'.

So StackOverflow, my question is how can I achieve this? Would I use a cursor inside of my InsteadOfInsertTrigger? I am looking for alternatives.

Can tests ever be deleted? If so, if the highest numbered one (for this year) is deleted, should the next inserted row have the same id as the one that was deleted? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 19, 2013 at 13:32
No, tests cannot be deleted. There will be a 'Status' column that will say cancelled or deleted etc. — Chris Sansone
– Chris Sansone, Commented Apr 19, 2013 at 13:39

Damien_The_Unbeliever · Accepted Answer · 2013-04-19 14:00:19Z

Not the neatest thing I'll ever write, but seems to do the job:

CREATE TRIGGER InsteadOfInsertTrigger ON dbo.TestTable
 INSTEAD OF INSERT AS
 BEGIN
    ;With Years as (
        select i.TestYear,
               COALESCE(MAX(tt.TestNumber),0) as YMax
        from inserted i left join TestTable tt
               on i.TestYear = tt.TestYear 
        group by i.TestYear
    ), Numbered as (
        select i.ID,i.Name,i.DateRequested,
               RIGHT('000' + CONVERT(varchar(4),
                  ROW_NUMBER() OVER (PARTITION BY i.TestYear
                                     ORDER BY i.DateRequested,i.Id) + YMax)
               ,4) as TestNumber
        from inserted i
            inner join
            Years y
                on
                    i.TestYear = y.TestYear
    )
    insert into TestTable (Id,Name,DateRequested,TestNumber)
    select Id,Name,DateRequested,TestNumber from Numbered;
 END;

The first CTE (Years) finds the highest used number for each year that's of interest. The second CTE (Numbered) then uses those values to offset a ROW_NUMBER() that's being assessed over all rows in inserted. I picked the ORDER BY columns for the ROW_NUMBER() so that it's as deterministic as possible.

(I was confused about one thing for a while, but it turns out that I can use TestYear from inserted rather than having to repeat the YEAR(DateRequested) formula)

Collectives™ on Stack Overflow

Multi-row action for Instead of trigger SQL Server

Scenario/background:

My Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Scenario/background:

My Question

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related