How can I make the trigger return a value?

I have a ‘roomstate’ table that represents states that I keep updated.

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?

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