MySQL Altering a Trigger to use a dynamic database name

Question

I have a perfectly working trigger. As I'm adding in more tests I'm finding that I want to alter this trigger to dynamically select the database based on the current database. In production this trigger resided in database rdata and talks to default. However in testing the trigger resides in test_rdata and talks to test_default. How can I modify the below trigger to dynamically select the right database

DROP TRIGGER IF EXISTS `af_rdata_data_trigger_4` $$
CREATE TRIGGER af_rdata_data_trigger_4
    AFTER INSERT on Results FOR EACH ROW
    BEGIN
        DECLARE project_id INT;
        -- Do this first - another table may have already created it..
        INSERT INTO default.rdata_project (`user`,`result_number`, `created_on`)
        VALUES (USER(), NEW.LBLDGRUNNO, NOW()) ON DUPLICATE KEY UPDATE last_update=NOW();
        -- Now push in our values 

        -- THIS IS WHAT NEEDS TO BE DYNAMIC!!
        UPDATE default.rdata_project project JOIN Results
        ON project.result_number = Results.LBLDGRUNNO SET
            project.annual_total_cost = Results.FTOTCOST
        WHERE project.result_number=NEW.LBLDGRUNNO;

    END$$
DELIMITER ;

Thanks much!

score 0 · Answer 1 · answered Jan 01 '13 at 05:26

0

You can create dynamic SELECT statement using STORED PROCEDURE and CALL that procedure from your TRIGGER. Check below code for example:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UpdateProject`$$

CREATE PROCEDURE `sp_UpdateProject`(IN _dbName VARCHAR(100), IN _LBLDGRUNNO INT)
BEGIN 
    SET @s = CONCAT("UPDATE ", _dbName, ".rdata_project project ");
    SET @s = CONCAT(@s, "JOIN Results ON project.result_number = Results.LBLDGRUNNO ");
    SET @s = CONCAT(@s, "SET project.annual_total_cost = Results.FTOTCOST ");
    SET @s = CONCAT(@s, "WHERE project.result_number=", _LBLDGRUNNO,";");
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

DROP TRIGGER IF EXISTS `af_rdata_data_trigger_4` $$
CREATE TRIGGER af_rdata_data_trigger_4
    AFTER INSERT ON Results FOR EACH ROW
    BEGIN
        DECLARE project_id INT;
        -- Do this first - another table may have already created it..
        INSERT INTO default.rdata_project (`user`,`result_number`, `created_on`)
        VALUES (USER(), NEW.LBLDGRUNNO, NOW()) ON DUPLICATE KEY UPDATE last_update=NOW();
        -- Now push in our values 

        -- THIS IS WHAT NEEDS TO BE DYNAMIC!!
        CALL sp_UpdateProject(dbName, NEW.LBLDGRUNNO);
    END$$
DELIMITER ;

answered Jan 01 '13 at 05:26

Saharsh Shah

28,687
8
48
83

There HAS to be a simpler way. Really is that it? Oh no... Thanks! – rh0dium Jan 01 '13 at 05:32
You can't use prepare statement in trigger so you have to create stored procedure to generate dynamic query – Saharsh Shah Jan 01 '13 at 05:42
Where is dbName set? And then where does that get changed out? – rh0dium Jan 01 '13 at 05:50
You have to pass the dbname in which you want to update the record when you call procedure. It depends on you what you want to pass in argument – Saharsh Shah Jan 01 '13 at 05:51
What if we go another route. How can I determine the database the trigger is running on. Then can't I do this? `IF REGEXP '^test_' THEN = test_` – rh0dium Jan 01 '13 at 05:56
That thing can be easily done in trigger and take this dbname in paremeter and pass it in argument and you can also do this in procedure – Saharsh Shah Jan 01 '13 at 06:05
@SaharshShah: I am also facing same issue but your solution is not correct as you are calling stored procedure from trigger and trigger will not allow prepare statement even in stored procedure...please execute your code it will give error like "Dynamic SQL is not allowed in stored function or trigger" – Zafar Malik Oct 07 '16 at 11:41

MySQL Altering a Trigger to use a dynamic database name

1 Answers1