how to use variable as a column name in trigger

Question

i try to create some trigger for my web database but there is not working code for my trigger here the problem

BEGIN
DECLARE t INT;
DECLARE b INT;
DECLARE ir INT;
DECLARE tgl INT;
DECLARE kol VARCHAR(3);



SET tgl = "(SELECT RIGHT(booking_checkin, 2) FROM pt_bookings WHERE booking_id = NEW.booking.id)";

SET ir = "(SELECT booked_room_id FROM pt_booked_rooms WHERE booking_id = NEW.booking.id)";

SET b = "(SELECT MID(booking_checkin , 6,2) FROM pt_bookings WHERE booking_id = NEW.booking_id)";

SET t = "SELECT IF((SELECT LEFT(booking_checkin, 4) FROM pt_bookings WHERE booking_id = NEW.booking_id) = 2019 ,'0','1')";

SET kol = "d" + tgl;

UPDATE pt_rooms_availabilities
set kol = kol + (SELECT `booking_nights` FROM `pt_bookings` WHERE booking_id = NEW.booking_id)
WHERE room_id = ir AND y = t AND m= b; 
END

the problem is var kol do not read as a column name maybe anyone can help me ?

Answer 1

It is not possible to do this in a trigger. In general, you could use dynamic sql to use variable column names, see e.g. Dynamic conversion of string into column name. This is however not allowed in stored functions or triggers.

You could use if then else to have a different insert statement for each situation, something like if tgl = 7 then update ... set d7 = d7 + ... elseif tgl = 122 then update ... set d122 = d122 + ....

In most situations though, you can and should avoid this by table design.

I am not sure what you are trying to do exactly, but you could just add a column tglcode to your table, store that value there and then add where tglcode = tgl to your update statement. It looks as if you (or your predecessor) did that or something similar with the variables b and t stored in columns m and t.

If that is a viable solution (or a proper table design) for your problem is hard to say though without more information. But you definitely cannot use the variable kol the way you did in a trigger.