CODEWITHSUNDEEP
mysqltriggersforeign-keys
Belmark Caday
Belmark Caday

Reputation: 1683

How to use triggers in Mysql

Guys I'm exploring triggers in MySQL and I'm a bit confused. I have created this tables:

persons

pid #primary key
last_name
first_name

then this table:

employee_hierarchy

emp_id #primary key
pid #foreign key
role

And created this trigger:

CREATE TRIGGER after_person_insert 
AFTER INSERT ON persons 
FOR EACH ROW 
INSERT INTO employee_hierarchy 
VALUES('','LAST_INSERT_ID()','employee');

TRIGGER Successfully created. But whenever I try to insert to persons table, i got a foreign key something error. So I dropped the foreign key and it was a success. But whats the use of this trigger thingy? Can you site a good example where this is useful? cause maybe this is not designed to update foreign keys, i dont know.

Upvotes: 0

Views: 99

Answers (1)

peterm
peterm

Reputation: 92785

Instead of trying to get a value from LAST_INSERT_ID() (BTW you insert a string since it is in quotes) you just need need to read assigned value from NEW.pid. It is available to you at this point AFTER INSERT of time.

CREATE TRIGGER after_person_insert 
AFTER INSERT ON persons 
FOR EACH ROW 
  INSERT INTO employee_hierarchy 
  VALUES(NULL, NEW.pid, 'employee');

Here is SQLFiddle demo

Upvotes: 1

Related Questions