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

I want to wrote a trigger for table users. when new user added, if his Title is IT related, then create a record in IT contact list table.
So I wrote below 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 'unknow column Title in the field list' But it did exist in table Users and IT_contact_list.So what's the issue?Thx.

CodePudding user response：

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