我有更新插入触发器
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 $相同的值 dbo.booking 上的c $ c>。
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
更多推荐
触发器在第一次操作时出错
发布评论