I have a ‘roomstate’ table that represents states that I keep updated.
x
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)
);
This table is updated when an employee assigns a room to a customer and shows the status of the rooms.
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
----------------------------------------------------------------
This is what happens when the employee assigns room 2 to customer 1 whose booking_id is 2.
select @custid = 2;
select @Bookingid := 2;
select @RoomAloct := 1;
UPDATE roomstate
SET booking_id = @Bookingid, cust_id = @custid, roomstate_state = 1
where roomstate.room_id = @RoomAloct;
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
----------------------------------------------------------------
As the table above is continuously updated, the existing contents may become NULL again.
I’d like to have a record of updating this in another table.
example :
--------------------------------------------------------------------------------------
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)
-----------------------------------------------------------------------------------------
So, I used trigger.
CREATE TABLE ROOMSTATElog
(
`RoomstateLOG_ID` INT NOT NULL AUTO_INCREMENT ,
`Room_ID` INT NOT NULL ,
`Cust_ID` INT NULL ,
`datetime` Datetime NULL ,
CONSTRAINT PRIMARY KEY (Roomstatelog_ID)
);
delimiter $$
create trigger roomstate_trigger
after update on ROOMSTATElog
for each row
begin
declare room_id int;
declare cust_id int;
set room_id = new.room_id;
set cust_id = new.cust_id;
insert into roomstatelog value (room_id,cust_id,now());
end $$
delimiter ;
I updated roomstate table but Trigger doesn’t work…
select * from ROOMSTATElog;
----------------------------------
RoomstateLOG_ID ROOM_ID CUst_ID Datetime
NULL NULL NULL NULL
-------------------------------------
What did I do wrong?
Advertisement
Answer
It seems the issue is in your trigger declaration. Change it to as below –
create trigger roomstate_trigger
after update on ROOMSTATE -- instead of ROOMSTATElog
For the new error you’re getting, you may specify the column names as well –
Insert into roomstatelog
(`Room_ID`,`Cust_ID`,`datetime`) -- you may also consider renaming datetime column to something else so that it doesn't clash with type DateTime
Values (room_id,cust_id,now()); -- notice VALUES instead of Value