创建检查另一个表中的日期的触发器

我必须编写一个触发器来检查MaintenanceDate是否大于或等于Plant的BirthDate(在另一个表中)。我已经写了一个函数，通过Id来显示Plant的生日。

我有问题如果我在维护表中插入一堆项目，触发器就不能正常工作。

CREATE TRIGGER TrgCheckBirthDateOfPlant
ON Maintenance
INSTEAD OF INSERT
AS  
DECLARE
@PlantId AS int,
@MaintenanceDate AS date;
BEGIN
SELECT @PlantId = PlantId 
FROM inserted;
SELECT @MaintenanceDate = MaintenanceDate 
FROM inserted;
IF (@MaintenanceDate >= dbo.GetBirthDateById(@PlantId))
INSERT INTO Maitenance 
SELECT I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM inserted I;
END

涉及的表有:

CREATE TABLE Maintenance
(
id int IDENTITY PRIMARY KEY,
PlantId int 
REFERENCES Plant(id) NOT NULL,
MaintenanceDate datetime NOT NULL,
description varchar(250),
type varchar(15)
);
CREATE TABLE Plant
(
id int IDENTITY PRIMARY KEY,
name varchar(30) NOT NULL,
birthDate NOT NULL,
height decimal (6, 1) CHECK (height<= 12000)
price decimal(10, 2) CHECK (price > 0),
);

触发器中需要包含一些内容。一般来说，返回额外的计数并不是一件好事。(我不确定返回什么计数代替触发器。包含以下语句的规则可能需要研究。我们不会同时需要原始插入计数和替换计数。如果有一个代替触发器，SQL Server是否会抑制原始插入的计数?)

SET NOCOUNT ON

无事可做的测试。@@ROWCOUNT(或bigint版本)不再可靠，因为源可以是merge语句。如果触发器在删除时触发，则测试"deleted"还需要表格。下面的第一个语句对于插入来说已经足够好了。第二种方法适用于插入、更新和删除。(不要同时使用)

IF NOT EXISTS(SELECT * FROM inserted) RETURN -- no rows inserted or updated
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) RETURN -- no rows inserted, updated, or deleted

现在插入的每条记录都需要测试。似乎每一种都来自不同的植物。我会检查函数中的逻辑，避免使用函数。在基于集合的查询中使用标量函数可能会降低性能。也许是这样。

INSERT INTO Maintenance 
SELECT I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM inserted I
LEFT JOIN Plant p
ON p.ID = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;

对于测试，您没有插入"表格你可以用真正的桌子。不要忘记排除插入。在一个好的数据样本上测试它，以确保你得到你想要的结果。我想现有的数据有要选择的日期。

SELECT Top 100 I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM Maintenance I
LEFT JOIN Plant p
ON p.Id = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;

这是一个比触发器更好的选择，是一个涉及索引视图的鲜为人知的技巧，它将强制执行多表约束。

基本上是这样的:

创建一个只包含两行的表:

CREATE TABLE dbo.TwoRows (dummy int);
INSERT dbo.TwoRows DEFAULT VALUES;
INSERT dbo.TwoRows DEFAULT VALUES;

然后创建一个视图，其中包含不符合约束的行，但将其与该表交叉连接。此视图将永远不会包含任何行。

CREATE VIEW dbo.CheckBirthDateOfPlant
WITH SCHEMABINDING  -- must be schame-bound
AS
SELECT 1 AS dummy
FROM dbo.Maintenance m
JOIN dbo.Plant p ON p.Id = m.PlantId
CROSS JOIN dbo.TwoRows
WHERE m.MaintenanceDate < p.birthDate;

在此视图上创建一个索引:

CREATE UNIQUE CLUSTERED INDEX CX_CheckBirthDateOfPlant
ON dbo.CheckBirthDateOfPlant (dummy);

现在，每当尝试插入或未满足约束的更新行时，服务器将尝试维护该索引视图。它将把行输入视图的联接，然后用TwoRows交叉联接。这使得它有两行对dummy具有相同的值，因此不具有唯一性。因此，插入/更新操作将完全回滚。

如果您真的想要将此作为触发器，你现有的代码有很多问题。

INSTEAD OF触发器可能很难管理，例如，如果基本表发生了变化，则需要修改它。你应该使用AFTER触发器。
你可以抛出一个异常来阻止插入。
必须考虑触发表中的多行(或零行)。
标量函数真的很慢，在这种情况下是不必要的。

CREATE OR ALTER TRIGGER TrgCheckBirthDateOfPlant
ON Maintenance
AFTER INSERT, UPDATE
AS  
SET NOCOUNT ON;
IF EXISTS (SELECT 1
FROM inserted i
JOIN Plant p ON p.Id = i.PlantId
WHERE i.MaintenanceDate < p.birthDate
)
THROW 50001, 'MaintenanceDate cannot be < p.birthDate', 0;

相关内容

最新更新

热门标签：