我有一个简化的表,如下所示:
I have a table that simplified looks like this:
create table Test ( ValidFrom date not null, ValidTo date not null, check (ValidTo > ValidFrom) )我想编写一个触发器,以防止插入与现有日期范围重叠的值。我写了一个看起来像这样的触发器:
I would like to write a trigger that prevents inserting values that overlap an existing date range. I've written a trigger that looks like this:
create trigger Trigger_Test on Test for insert as begin if exists( select * from Test t join inserted i on ((i.ValidTo >= t.ValidFrom) and (i.ValidFrom <= t.ValidTo)) ) begin raiserror (N'Overlapping range.', 16, 1); rollback transaction; return end; end但这没用,因为我新插入的记录是两者的一部分触发器中的 Test 和 insert 表。因此,插入表中的新记录总是在Test表中与其自身联接。触发器将始终还原翻译。
But it doesn't work, since my newly inserted record is part of both tables Test and inserted while inside a trigger. So the new record in inserted table is always joined to itself in the Test table. Trigger will always revert transation.
我无法将新记录与现有记录区分开。因此,如果我排除相同的日期范围,则可以在表格中插入多个完全相同的范围。
I can't distinguish new records from existing ones. So if I'd exclude same date ranges I would be able to insert multiple exactly-same ranges in the table.
是否可以编写一个可以按预期工作的触发器,而无需在我的测试表中添加其他标识列,我可以使用该列从我的 exists()中排除新插入的记录语句,例如:
Is it possible to write a trigger that would work as expected without adding an additional identity column to my Test table that I could use to exclude newly inserted records from my exists() statement like:
create trigger Trigger_Test on Test for insert as begin if exists( select * from Test t join inserted i on ( i.ID <> t.ID and /* exclude myself out */ i.ValidTo >= t.ValidFrom and i.ValidFrom <=t.ValidTo ) ) begin raiserror (N'Overlapping range.', 16, 1); rollback transaction; return end; end重要:如果不可能没有身份是唯一的答案,欢迎您提出它,并给出合理的解释。
Important: If impossible without identity is the only answer, I welcome you to present it along with a reasonable explanation why.
推荐答案两个小改动
首先,在触发器中添加where子句,以将重复的记录从联接中排除。这样您就不会将插入的记录与其自身进行比较:
First, add a where clause to your trigger to exclude the duplicate records from the join. Then you won't be comparing the inserted records to themselves:
select * from testdatetrigger t join inserted i on ((i.ValidTo >= t.ValidFrom) and (i.ValidFrom <= t.ValidTo)) Where not (i.ValidTo=t.Validto and i.ValidFrom=t.ValidFrom)除了,这将允许精确的重复范围,因此您必须在两列之间添加唯一约束。实际上,您可能希望在每一列上使用唯一的约束,因为默认情况下,同一天开始(或结束)的任何两个范围都是重叠的。
Except, this would allow for exact duplicate ranges, so you will have to add a unique constraint across the two columns. Actually, you may want a unique constraint on each column, since any two ranges that start (or finish) on the same day are by default overlapping.
更多推荐
防止使用SQL触发器插入重叠的日期范围
发布评论