mysql - 每次在mysql中更新时，如何使触发器累积数据？

Question

我有一个代表我不断更新的状态的“房间状态”表。

CREATE TABLE ROOMSTATE
(
    `RoomState_ID`          INT       NOT NULL   AUTO_INCREMENT ,  
    `Room_ID`          INT       NOT NULL    ,
    `RoomState_State`  BOOL      NULL       , 
    `Cust_ID`          INT       NULL   ,
    `Booking_ID`       INT       NULL   ,
    CONSTRAINT  PRIMARY KEY (Roomstate_ID)
);

当员工将房间分配给客户并显示房间状态时，此表会更新。

INSERT INTO ROOMSTATE(Room_ID, RoomState_State, Cust_ID, Booking_ID) 
 (select room_id, '0', null, null from room);



select * from roomstate;

-------------------------------------------------------------------
Roomstate_ID  ROOM_ID  ROOMSTATE_STATE  CUST_ID  BOOKING_ID
     1           1           0           NULL        NULL
     2           2           0           NULL        NULL
     3           3           0           NULL        NULL
     4           4           0           NULL        NULL
     5           5           0           NULL        NULL      
----------------------------------------------------------------

当员工将房间 2 分配给 booking_id 为 2 的客户 1 时，就会发生这种情况。

select @custid = 2;
select @Bookingid := (select booking_id from booking where cust_id = @custid),
@RoomAloct := 1;

UPDATE roomstate
SET booking_id = @Bookingid
where roomstate.room_id = @RoomAloct;

UPDATE roomstate
INNER JOIN booking ON roomstate.booking_id = booking.booking_id
SET roomstate.cust_id=booking.cust_id;

UPDATE roomstate
SET roomstate_state = 1
where roomstate.cust_id IS NOT NULL ;

select * from roomstate;

-------------------------------------------------------------------
Roomstate_ID  ROOM_ID  ROOMSTATE_STATE  CUST_ID  BOOKING_ID
     1           1           1             2          2
     2           2           0           NULL        NULL
     3           3           0           NULL        NULL
     4           4           0           NULL        NULL
     5           5           0           NULL        NULL      
----------------------------------------------------------------

由于上表不断更新，现有内容可能再次变为NULL。

我想在另一个表中记录更新它。

例子 ：

--------------------------------------------------------------------------------------
PK_LOGTB  ROOM_ID  ROOMSTATE_STATE  CUST_ID  BOOKING_ID  DATETIME
     1           1           1             2        2      2021-06-06 00:00:00(NOW)
     2           2           1             5        3      2021-06-06 00:00:00(NOW)
     3           3           1             8        4      2021-06-06 00:00:00(NOW)
     4           1           0             2        2      2021-06-07 00:00:00(NOW)
-----------------------------------------------------------------------------------------

我怎样才能得到这个日志表？我想我必须写一个触发器。

标签： mysqlsqltriggers