触发器在第一次操作时出错

本文介绍了触发器在第一次操作时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有更新插入触发器

CREATE TRIGGER tgBooking_update_seat ON dbo.booking AFTER INSERT, UPDATE AS BEGIN IF EXISTS (SELECT * FROM INSERTED) BEGIN -- Handle insert or update UPDATE Seats SET status = Bookings.statusBooked FROM dbo.seat Seats INNER JOIN INSERTED Bookings ON Bookings.noSeat = Seats.noSeat END END

这是我的桌子座位。该表是我的座位列表，该表具有 status 列，我默认将其设置为0。

This is my table seat. This table is list of my seats, this table has a status column which I set to 0 by default.

这是我的表格预订。默认情况下，我将 statusBooked 设置为1的每个插入操作。如果 end 等于当前时间，则 statusBooked 将自动更新为0。

And this is my table booking. Every insert action I set statusBooked to 1 by default. If end is equal to current time then statusBooked will automatically updated to 0.

I想要自动将 dbo.seat 上的状态更新为与 statusBooked 。

I want to automatically update status on dbo.seat to the same value as statusBooked on dbo.booking.

• 如果已预订状态为1，则状态应更新为1
• 如果 statusBooked 更新为0，则 status 也必须更新为0

• If statusbooked is 1, then status should be updated to 1
• If statusBooked updates to 0, then status must be updated to 0 also

因此状态为参考 statusBooked 通过noSeat。我编写了触发器，它就起作用了。

So status is reference statusBooked by noSeat. I write the trigger and it works.

但是在第一次（当所有 dbo.seat 状态为0时）。

But at first time (when all dbo.seat status is 0.) it won't work.

示例：

（当状态全部为0）我确实插入了 booking （noSeat：1-5）， statusBooked 原为1，但 status （noSeat：1-5）保持0。然后我确实进行了第二次插入，就可以了。我再次插入 booking （noSeat：1-7） statusBooked 是1而 status （noSeat：1-7）也是1。但noSeat 1-5的状态仍为0。

(when status is all 0) I did insert to booking(noSeat : 1-5), statusBooked was 1 but status(noSeat:1-5) stay 0. then I did insert for second time, it just works. I insert to booking again (noSeat : 1-7)statusBooked is 1 and status(noSeat : 1-7) is also 1. but status for noSeat 1-5 is still 0.

我根本只有1个触发器。那么问题是什么？

I only have 1 trigger at all. so what's the problem ?

推荐答案

FROM dbo.seat Seats INNER JOIN INSERTED Bookings ON Bookings.noSeat = Seats.noSeat

我认为您不需要此区域触发器中的代码，因为您已经提到要更新该行中的Seats表：

I dont think you need this block of code in your trigger because you already mentioned that you are updating your Seats table in the line:

UPDATE Seats SET

我建议您这样重写并重试：

I would suggest you to rewrite it like this and try again:

UPDATE Seats SET status = Bookings.statusBooked WHERE Bookings.noSeat = Seats.noSeat

我也建议您尝试使用关键字 new，而不要插入，因为我自己的触发器有一些问题，我也得到了这个建议。希望对您有帮助！ 这是我对触发器的问题...有人回答了，您也可以参考它，这里是链接：

Also I would suggest you to try and use the keyword 'new' instead if INSERTED as I had some problems with triggers myself and I got this suggestion. Hope it helps you! This was my question on triggers...Someone answered it, You can refer to it as well, here is the link:

我在使用sql创建触发器的代码中出现语法错误。我使用的是MySQL版本14.14