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.

Answer 1

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.

Answer 2

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 ;