sql server 2008在2个表上多次插入
我在 sql server 2008 数据库上收到以下触发器
CREATE TRIGGER tr_check_stoelen
ON Passenger
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS(
SELECT 1
FROM Passenger p
INNER JOIN Inserted i on i.flight= p.flight
WHERE p.flight= i.flightAND p.seat= i.seat
)
BEGIN
RAISERROR('Seat taken!',16,1)
ROLLBACK TRAN
END
END
当我尝试运行下面的查询时，触发器抛出错误。这个查询我应该在两个不同航班的数据库中插入两个不同的乘客。我确信两个座位都没有被占用，但我不明白为什么触发器会给我这个错误。它必须与相关性有关吗？
INSERT INTO passagier VALUES
(13392,5315,3,'Janssen Z','2A','October 30, 2006 10:43','M'),
(13333,5316,2,'Janssen Q','2A','October 30, 2006 11:51','V')
更新： 该表如下所示
CREATE TABLE Passagier
(
passengernumber int NOT NULL CONSTRAINT PK_passagier PRIMARY KEY(passagiernummer),
flight int NOT NULL CONSTRAINT FK_passagier_vlucht REFERENCES vlucht(vluchtnummer)
ON UPDATE NO ACTION ON DELETE NO ACTION,
desk int NULL CONSTRAINT FK_passagier_balie REFERENCES balie(balienummer)
ON UPDATE NO ACTION ON DELETE NO ACTION,
name varchar(255) NOT NULL,
seat char(3) NULL,
checkInTime datetime NULL,
gender char(1) NULL
)
I got the following trigger on my sql server 2008 database
CREATE TRIGGER tr_check_stoelen
ON Passenger
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS(
SELECT 1
FROM Passenger p
INNER JOIN Inserted i on i.flight= p.flight
WHERE p.flight= i.flightAND p.seat= i.seat
)
BEGIN
RAISERROR('Seat taken!',16,1)
ROLLBACK TRAN
END
END
The trigger is throwing errors when i try to run the query below. This query i supposed to insert two different passengers in a database on two different flights. I'm sure both seats aren't taken, but i can't figure out why the trigger is giving me the error. Does it have to do something with correlation?
INSERT INTO passagier VALUES
(13392,5315,3,'Janssen Z','2A','October 30, 2006 10:43','M'),
(13333,5316,2,'Janssen Q','2A','October 30, 2006 11:51','V')
UPDATE:
The table looks as below
CREATE TABLE Passagier
(
passengernumber int NOT NULL CONSTRAINT PK_passagier PRIMARY KEY(passagiernummer),
flight int NOT NULL CONSTRAINT FK_passagier_vlucht REFERENCES vlucht(vluchtnummer)
ON UPDATE NO ACTION ON DELETE NO ACTION,
desk int NULL CONSTRAINT FK_passagier_balie REFERENCES balie(balienummer)
ON UPDATE NO ACTION ON DELETE NO ACTION,
name varchar(255) NOT NULL,
seat char(3) NULL,
checkInTime datetime NULL,
gender char(1) NULL
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此子查询存在一些问题：
首先，
WHERE p.flight = i.flight是完全不必要的，因为它已经是联接的一部分。其次，
p.seat = i.seat也应该是JOIN的一部分。第三，此触发器在插入行之后运行，因此这将始终匹配，因此您的触发器将始终引发错误并滚动后退。
您可以修复触发器，但更好的方法是根本不使用触发器。如果我理解您想要正确执行的操作，那么您所需要的只是对
航班，座位进行UNIQUE约束：There are a few problems with this subquery:
First of all, the
WHERE p.flight = i.flightis quite unnecessary, as it's already part of your join.Second, the
p.seat = i.seatshould also be part of theJOIN.Third, this trigger runs after the rows have been inserted, so this will always match, and your trigger will therefore always raise an error and roll back.
You can fix the trigger, but a much better method would be to not use a trigger at all. If I understand what you're trying to do correctly, all you need is a
UNIQUEconstraint onflight, seat:如果您在插入记录后运行触发器，然后查找包含刚刚插入的值的记录，您总会找到它。您可以尝试使用 INSTEAD OF 触发器，以便可以在实际执行插入之前检查现有记录。
If you run your trigger after inserting a record, and then look for a record with the values you just inserted, you will always find it. You might try an INSTEAD OF trigger so you can check for an existing records before actually doing the insert.
它可能会通过在表中找到自身来抛出错误（循环引用回自身）。您可能需要向 where 子句添加一个额外的过滤器，例如“ AND Passenger.ID <> insert.ID ”
It might be throwing the error by finding itself in the table (circular reference back to itself). You might want to add an additional filter to the where clause like " AND Passenger.ID <> inserted.ID "