0

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 ?

aynber
  • 22,380
  • 8
  • 50
  • 63
max_alexander
  • 17
  • 4

1 Answers1

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.

Solarflare
  • 10,721
  • 2
  • 18
  • 35
  • oh i see the problem i think i should trying to use dynamic sql thanks for your answer – max_alexander Jul 02 '19 at 07:34
  • Not sure if you saw it (or if I misunderstood your comment), but unfortunately you cannot use dynamic sql in a trigger (just in stored procedures). (You may be able to redesign you app in a way that you insert/update into that table via a stored procedure so you do not need a trigger to do the afterwork). – Solarflare Jul 02 '19 at 08:03
  • my problem is solved i use the stored procedure to update my table, i think the trigger is the only way , but now i find 2 way to this case 1- with stored procedure 2 - just trick your php code and it works ;) – max_alexander Jul 05 '19 at 20:02