have the following MySQL trigger which works fine except for the final part where I use the variable @Shifts:

INSERT INTO `oee_main_interim_final` (id,NAME,ts,Left_IO,Left_NIO,Recovery,Right_IO,Rig ht_NIO,RunMode,S_TYPE,Shift,STD,curr_S_Type,Report ing_Date, Shift_TS)
VALUES(NULL, New.NAME, New.TS, NEW.Left_IO, New.Left_NIO, New.Recovery, New.Right_IO, New.Right_NIO, New.RunMode, New.S_TYPE,


(select @Shifts := ( Select

(Case
When ((CurTime() > oee_machinenames.Shift1) And
(CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
When ((CurTime() > oee_machinenames.Shift2) And
(CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
When ((CurTime() > oee_machinenames.Shift3) Or
(CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End)
From
oee_machinenames
where
oee_machinenames.ID = New.NAME

Group By
oee_machinenames.ID) As Shift),


(Select
`STD` From `oee_variant` Where `Machine_ID` = New.NAME And `S_TYPE` =

(Select
`S_TYPE`

From
`v_getmaxid`
Where
`NAME` = New.Name And
v_getmaxid.Max_id In (Select
Max(v_getmaxid.Max_id) As Max_Max_id
From
`v_getmaxid`
Where
`NAME` = New.Name))

And `oee_variant`.`Operators` =
(Select `Operators` from `oee_machinenames` where `ID` = New.NAME)),


(Select
`S_TYPE`

From
`v_getmaxid`
Where
`NAME` = New.Name And
v_getmaxid.Max_id In (Select
Max(v_getmaxid.Max_id) As Max_Max_id
From
`v_getmaxid`
Where
`NAME` = New.Name)),

(select((case when (((@Shifts = 'Shift3') and
(cast(New.TS as time) >= '00:00:01'))
or (cast(New.TS as time) <= '05:59:00'))
then (New.TS - interval 1 day) else cast(New.TS as date) end)) AS Reporting_Date),

(select @Shifts from `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME)

)
This part : (select @Shifts fromoee_machinenameswhereoee_machinenames.ID= New.NAME) should go in table called oee_machinenames which consists of the following:

No code has to be inserted here.

etc

and select either Shift1, Shift2 or Shift3 as columns depending on the variable @Shifts in the query and return the respective time. My query is not returning the time but returning 'Shift1'. I want to show the time not the shift name.