search

Home  >  Q&A  >  body text

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

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粉277305212P粉277305212315 days ago458

reply all(1)I'll reply

  • P粉448130258

    P粉4481302582024-04-04 10:30:24

    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

    reply
    0
  • Cancelreply