MySQL Trigger creation

Question

I have an application where I need to INSERT an auto_increment value from a PK in another table. I know how to do this in PHP, but I need to have this done at the DB level, since I cannot change the program logic.

I am new to triggers, so I'm sure this will be an easy answer for someone. Here is what I have so far:

DELIMITER //
     CREATE TRIGGER new_project AFTER INSERT ON m_quality_header
     FOR EACH ROW
     BEGIN
         INSERT INTO m_quality_detail (d_matl_qa_ID) VALUES  (NEW.h_matl_qa_ID);
     END//
DELIMITER ;

I just want the value of the auto_increment value from h_matl_qa_ID to be inserted as a new record into d_matl_qa_ID. The error I get is:

"This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

But, I don't want to update the table that has the trigger, so why is my current code considered a 'multiple' trigger?

This is on MySQL 5.0.45-7.el5 running on a CentOS 5 server (64-bit Intel) If I have to, I can modify the PHP code, but that needs to be the last resort.

Have you defined another `AFTER INSERT ON m_quality_header` trigger? Use `SHOW TRIGGERS WHERE event='INSERT' AND timing='AFTER' AND \`table\`='m_quality_header'` to check (http://dev.mysql.com/doc/refman/5.0/en/show-triggers.html). — outis, Apr 01 '10 at 17:51
At a guess, `BEFORE EACH ROW` is considered a multiple trigger, because it fires once for each inserted row. — Powerlord, Apr 01 '10 at 17:51
OK, I got my Trigger working properly now.. Here is the final code that I needed for this to work the way I wanted: DELIMITER // DROP TRIGGER IF EXISTS new_project// CREATE TRIGGER new_project AFTER INSERT ON m_quality_header FOR EACH ROW BEGIN INSERT INTO m_quality_detail (d_matl_qa_ID, d_matl_qa_project_test_number) VALUES (LAST_INSERT_ID(), LAST_INSERT_ID()); END// DELIMITER ; — bgarlock, Apr 01 '10 at 18:46

itsmatt · Accepted Answer · 2010-04-01T18:37:59.393

0

If you've tried to create the trigger before, as outis states above you can issue the command

SHOW TRIGGERS;

and it will tell you.

If that is the case, what I typically do is issue

DROP TRIGGER IF EXISTS my_trigger_name;

and then recreate the trigger.

Your trigger code, otherwise, looks OK.

edited Apr 01 '10 at 18:37

answered Apr 01 '10 at 18:32

itsmatt

31,265
10
100
164

score 0 · Answer 2 · answered Apr 01 '10 at 18:49

DELIMITER //
    DROP TRIGGER IF EXISTS new_project//
    CREATE TRIGGER new_project AFTER INSERT ON m_quality_header
    FOR EACH ROW
    BEGIN
        INSERT INTO m_quality_detail 
        (d_matl_qa_ID, d_matl_qa_project_test_number) VALUES (LAST_INSERT_ID(),     LAST_INSERT_ID());
    END//
DELIMITER ;

MySQL Trigger creation

2 Answers2