How to use select..into statement inside trigger in mySQL

Question

I am getting the syntax error as

"select" is not valid at this position for this server version, expecting: '(', with

delimiter |
CREATE TRIGGER after_temp_ticket_insert 
AFTER INSERT ON temp_tickets
FOR EACH ROW begin
declare 
    ss varchar(5);

    select system into ss from support_system where team=new.assigned_team;

    INSERT INTO tickets(ticket_id,ticket_type,summary,assigned_team,assigned_to,created_by,created_date,priority,status,resolution,resolved_date,support_system,category_tier1,category_tier2)
    VALUES (new.ticket_id,new.ticket_type,new.summary,new.assigned_team,new.assigned_to,new.created_by,new.created_date,new.priority,new.status,new.resolution,new.resolved_date,ss,NULL,NULL);
end
|
delimiter ;

Kindly help me with this. Thanks in advance.

score 1 · Accepted Answer · answered Apr 19 '20 at 12:26

Use backticks for system it is a reserved word

Also check here for more information

delimiter |
CREATE TRIGGER after_temp_ticket_insert 
AFTER INSERT ON temp_tickets
FOR EACH ROW begin
declare 
    ss varchar(5);

    select `system` into ss from support_system where team=new.assigned_team;

    INSERT INTO tickets(ticket_id,ticket_type,summary,assigned_team,assigned_to,created_by,created_date,priority,status,resolution,resolved_date,support_system,category_tier1,category_tier2)
    VALUES (new.ticket_id,new.ticket_type,new.summary,new.assigned_team,new.assigned_to,new.created_by,new.created_date,new.priority,new.status,new.resolution,new.resolved_date,ss,NULL,NULL);
end
|
delimiter ;

How to use select..into statement inside trigger in mySQL

1 Answers1