How to write MySql insert trigger based on inserted data value?

Question

I want to write a trigger for table users. When a new user is added, if his title is IT related, a record is created in the IT Contact List table.

So I wrote the following trigger.

CREATE TRIGGER `test1` INSERT ON `Users` FOR EACH ROW BEGIN
IF INSTR(Title,'IT') > 0
THEN
INSERT INTO IT_contact_list (name,title) value (username,Title);
END IF;
END;

It has error "Unknown column header in field list" but it does exist in tables Users and IT_contact_list. So what's the problem? Thanks.

P粉448130258 · Answer

Fixed and tested in workbench. Try this:

delimiter //
drop trigger if exists test1 //
CREATE TRIGGER `test1` AFTER INSERT ON `Users` FOR EACH ROW BEGIN
IF INSTR(new.Title,'IT') > 0
THEN
INSERT INTO IT_contact_list (name,title) value (new.username,new.Title);
END IF;
END//

insert into Users values('john','HardwareIT');
insert into Users values('bill','Hardware engineer');
select * From IT_contact_list;

--result set:
 john    HardwareIT