Многострочный экшн вместо триггера SQL Server
Сценарий / фон:
Я пытаюсь создать таблицу "Тесты". Для целей этого вопроса моя таблица будет иметь только 5 столбцов, определенных следующим образом:
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
Мое требование заключается в том, что я хочу, чтобы "TestNumber" автоматически увеличивался в зависимости от года. Например:
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
Я думал, что это будет столбец с автоинкрементом, но как мне его сбросить, основываясь на том, что это первый тест нового года? Таким образом, мое неправильное решение до сих пор было триггером "вместо вставки", определенным следующим образом:
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
Теперь вот некоторые DML, показывающие триггер в действии:
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;
http://i48.tinypic.com/35kkggz.jpg
Таким образом, как вы можете видеть, мой триггер работает для однорядных вставок, но зоркий глаз сможет сказать, что он не будет работать для многострочных вставок.
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;
Мой вопрос
Я понимаю, что могу, вероятно, справиться с этим с помощью хранимых процедур и заставить пользователей использовать хранимые процедуры при обновлении таблиц, но я хочу быть очень осторожным и не дать системным администраторам возможности делать прямые вставки в таблицу с неверным "TestNumber".
Итак, Stackru, мой вопрос: как мне этого добиться? Буду ли я использовать курсор внутри моего вместо OfInsertTrigger? Я ищу альтернативы.
1 ответ
Не самая лучшая вещь, которую я когда-либо напишу, но, кажется, делает работу:
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;
Первый CTE (Years
) находит наибольшее используемое число для каждого интересующего года. Второй CTE (Numbered
) затем использует эти значения для смещения ROW_NUMBER()
это оценивается по всем строкам в inserted
, Я выбрал ORDER BY
столбцы для ROW_NUMBER()
так что это как можно более детерминировано.
(Я был смущен одной вещью на некоторое время, но оказывается, что я могу использовать TestYear
от inserted
вместо того, чтобы повторять YEAR(DateRequested)
формула)