触发以阻止一些更新并允许插入 [英] Trigger to block some updates and allow inserts
问题描述
我有一个SQL Server表，该表可以跟踪客户名称的更改：
I've got a SQL Server table which keeps track of customer name changes:
CREATE TABLE CustomerHistory
(
Id INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
Name VARCHAR(255) NOT NULL,
ValidFrom DATETIME NOT NULL,
ValidTo DATETIME NOT NULL,
CreatedOn DATETIME NOT NULL,
ModifiedOn DATETIME NOT NULL
)
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn )
VALUES (1, 'ABC', '1900-01-01','2999-12-31', '2015-07-03 11:29:23.000', '2015-07-03 11:29:23.000')
应用程序允许用户以两种方式进行更改，即通过更改当前记录（ 名称和 ModifiedOn 已更新）或通过插入新记录
Application allows user to make changes in two ways i.e. either by changing current record (Name and ModifiedOn are updated) or by inserting new record
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn)
VALUES (1,'AAB', '2015-07-04','2999-12-31', '2015-07-04 12:29:23.000', '2015-07-04 12:29:23.000')
并更新上一个（名称，ValidTo，ModifiedOn 已更新）。由于以第一种方式进行了一些不需要的更改（仅更新当前记录），因此我需要将其阻止，因此以第二种方式进行每个更改（插入新记录并更新先前的记录）。我需要使用触发器来做到这一点，以便用户获得特殊的错误信息。
and updating previous one (Name, ValidTo, ModifiedOn are updated). Due to several unwanted changes done in the first way (updating current record only) I need to block it so each change made is done in the second way (inserting new record and updating previous one). I need to do it with trigger so users gets special error information.
任何想法如何应对？
推荐答案
例如，可以使用此触发器：
You can use this trigger for example:
CREATE TRIGGER dbo.TR_InvalidateOldRows
ON CustomerHistory
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Debug
--SELECT *
--FROM inserted
UPDATE ch
SET ValidTo = GETDATE(), ModifiedOn = GETDATE()
FROM (
SELECT TOP 1 Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM (
SELECT Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM CustomerHistory AS ch
WHERE ch.CustomerId = (SELECT CustomerId FROM inserted)
EXCEPT
SELECT Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM inserted
) as allRows
ORDER BY ID DESC
) as oldRow
INNER JOIN CustomerHistory as ch
ON oldRow.id = ch.Id
END
GO
使用以下示例数据填充
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn)
VALUES (1,'AAB', GETDATE(),DATEADD(year,1,GETDATE()), GETDATE(), GETDATE())
表中填充：
Id CustomerId Name ValidFrom ValidTo CreatedOn ModifiedOn
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
1 1 AAB 2015-07-04 13:21:34.500 2016-07-04 13:21:34.500 2015-07-04 13:21:34.500 2015-07-04 13:21:34.500
如果您运行插入再次将导致以下结果：
If you run the Insert again it will result in this:
Id CustomerId Name ValidFrom ValidTo CreatedOn ModifiedOn
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
1 1 AAB 2015-07-04 13:21:34.500 2015-07-04 13:22:02.163 2015-07-04 13:21:34.500 2015-07-04 13:22:02.163
2 1 AAB 2015-07-04 13:22:02.153 2016-07-04 13:22:02.153 2015-07-04 13:22:02.153 2015-07-04 13:22:02.153
此触发器只会使所有触发器无效特定 CustomerId 的旧行。
This trigger will just invalidate all older rows of a specific CustomerId.
如果您还想设置 ModifiedOn 日期，如果发生更新，则可以创建此附加触发器：
If you want additionally to set the ModifiedOn date if an update occurs you can create this additional trigger:
CREATE TRIGGER dbo.TR_UpdateModifiedOn
ON CustomerHistory
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Debug
--SELECT *
--FROM inserted
UPDATE ch
SET ModifiedOn = GETDATE()
FROM inserted as i
INNER JOIN CustomerHistory as ch
ON i.id = ch.Id
END
GO
顺便说一句，处理此问题的过程胜于触发器。但是在某些情况下，您无法提供程序。例如，如果用户将使用 SSMS ， Access 或通过任何其他应用程序直接更新来操纵数据。
By the way, a procedure which handles this would be better than a trigger. But in some cases you can't provide a procedure. For example if users will manipulate the data using SSMS, Access or direct updates through any other applications.
根据反馈进行编辑
我将旧代码放在上面，以防万一其他人需要如上所述的解决方案。在您的情况下，您只想阻止所有更新，除了它们仅更新 ModifiedOn 和 ValidTo 列。
I leave the old code above just in case someone else needed a solution like described above. In your case you just want to block all Updates except they update only the ModifiedOn and the ValidTo columns.
在这种情况下，此触发器将解决该问题：
In this case this trigger will solve it:
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Add your conditions here
IF (
-- e.g. Updated a row without modifieng the ModifiedOn Column
SELECT COUNT(*)
FROM inserted as i
INNER JOIN deleted as d
ON i.Id = d.Id
AND i.CustomerId = d.CustomerId
AND i.Name = d.Name
AND i.ValidFrom = d.ValidFrom
AND i.CreatedOn = d.CreatedOn
AND i.ValidTo <> d.ValidTo
AND i.ModifiedOn <> d.ModifiedOn
) = 0 BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
此代码将引发异常：
-- not allowed
UPDATE CustomerHistory
SET Name = N'EEE'
WHERE id = 2
虽然这将起作用：
-- allowed
UPDATE CustomerHistory
SET ModifiedOn = GETDATE(),
ValidTo = DATEADD(day,1,GETDATE())
WHERE id = 2
另一个附加项可以是<$ c $内的 INSTEAD OF -Trigger内的 WHERE 子句c> IF -条款。如果插入时间早于60秒（例如），这将阻止在提到的两列上进行更新。可以通过将此行添加到 IF ：
Another addition can be an WHERE-clause inside the INSTEAD OF-Trigger inside the IF-clause. Which will prevent updates on the two mentioned columns, if the Insert is older than 60 seconds (for example). This can be achieved by adding this row to the IF:
WHERE DATEDIFF(SECOND,d.CreatedOn,i.ModifiedOn) < 60
以下代码的结果：
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Add your conditions here
IF (
-- e.g. Updated a row without modifieng the ModifiedOn Column
SELECT COUNT(*)
FROM inserted as i
INNER JOIN deleted as d
ON i.Id = d.Id
AND i.CustomerId = d.CustomerId
AND i.Name = d.Name
AND i.ValidFrom = d.ValidFrom
AND i.CreatedOn = d.CreatedOn
AND i.ValidTo <> d.ValidTo
AND i.ModifiedOn <> d.ModifiedOn
WHERE DATEDIFF(SECOND,d.CreatedOn,i.ModifiedOn) < 60
) = 0 BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
添加真正宽泛的表
如果您有一个包含很多列的真正宽泛的表，并且您将不会维护所有列，要添加允许的列，您可能可以使用以下触发器，该触发器使用哈希来比较旧行和新行。它将按照上述相同的原理工作，但使用动态哈希算法。
If you have a really broad table with many columns and you won't to maintain all columns and just want to add the allowed columns you can probably use the following trigger which uses a hash to compare the old and the new row instead. It will work after the same principle of the above one but uses a dynamic hash algorithm.
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max), @hash_before varbinary(max),
@hash_after varbinary(max), @columnlist nvarchar(max),
@paramDefinition nvarchar(500) = N'@hash_value varbinary(max) OUTPUT';
SELECT @columnlist = COALESCE(
@columnlist + N'+ISNULL(CONVERT(nvarchar(max),'+ COLUMN_NAME + N'),N'''')',
N'ISNULL(CONVERT(nvarchar(max),'+ COLUMN_NAME + N'),N'''')')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'CustomerHistory'
-- Which columns are allowed to be updated?
AND COLUMN_NAME NOT IN(N'ModifiedOn',N'ValidTo')
-- needed due to scope of deleted and inserted
SELECT * INTO #deleted FROM deleted
-- Get the hash-value for the before-values
SET @sql = N'
SELECT @hash_value = HASHBYTES(''SHA1'','+@columnlist+')
FROM #deleted'
EXECUTE sp_executesql @sql, @paramDefinition, @hash_value = @hash_before OUTPUT;
DROP TABLE #deleted
SELECT * INTO #inserted FROM inserted
-- Get the hash-value for the after-values
SET @sql = N'
SELECT @hash_value = HASHBYTES(''SHA1'','+@columnlist+')
FROM #inserted'
EXECUTE sp_executesql @sql, @paramDefinition, @hash_value = @hash_after OUTPUT;
DROP TABLE #inserted
SELECT @hash_before, @hash_after
IF (@hash_before <> @hash_after) BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
这篇关于触发以阻止一些更新并允许插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
