0

I have 4 tables comment,employee,mode and module(all are default NULL).

I want to add an after update trigger in my comment table when emp_id,mode_id,module_id from comment table(which are foreign key here) updated it will record into the new activity table with timestamp.

All I need to show First name(from employee table), last name(from employee table), tweet(from comment table),timestamp(current time),mode_name(from mode table) and module_name(from module table).

when I created a trigger and when i update single row in my comment table it will put 10419 records in my activity table it supposed to put just 1 record instead of 10419.

Thanks in advance.

CREATE 
DEFINER=`root`@`localhost` 
TRIGGER `rewardco3`.`comment_AFTER_UPDATE` 
AFTER UPDATE 
ON `comment` 
FOR EACH ROW
BEGIN
INSERT INTO activity(
        Emp_ID,
        First_name,
        last_name,
        tweet,
        mode_name,
        module_name
        )
        select
        new.Emp_ID,
        e.First_name,
        e.last_name,
        old.tweet,
        m.mode_name,
        ml.module_name
    FROM comment c,employee e, mode m, module ml
    where new.Emp_ID=e.Emp_ID AND new.mode_id=m.mode_id AND new.module_id=ml.module_id;
END
  • Your INSERT statement contains comment table in FROM clause of SELECT. It is needless (you use OLD and NEW pseudotables for accessing the data from this table already), remove it. – Akina Sep 8 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.