Create Trigger inside stored procedure in mysql-CodePudding

I want to create an trigger inside an procedure. but after some research I got to know that it is not possible. can u suggest me another way I can achieve the below actions. (I cant share exact data and queries due to some reason. please refer similar queries.)

What I want

I have created an temporary table containing data i need.
eg. CREATE TEMPORARY TABLE temp1 SELECT id, col_1 FROM table1 WHERE col_1=2;

I want to inset data in table table2 when data is inserted in temp1, which i can achieve by creating a TRIGGER. but the problem is I want to give a value in table2 which will be dynamic and will be taken from nodejs backend. so i created a PROCEDURE which takes parameter neededId. but i cant created trigger inside a procedure. is their any other way i can achieve this?

Procedure I Created

here neededId is the foreign key I get from backend to insert


DELIMITER $$
USE `DB`$$
CREATE PROCEDURE `MyProcedure` (IN neededID int)
BEGIN
    DROP TABLE IF EXISTS temp1;
    CREATE TEMPORARY TABLE temp1 SELECT id, col_1 FROM table1 WHERE col_1=2;

    
    DROP TRIGGER IF EXISTS myTrigger;
    CREATE TRIGGER myTrigger AFTER INSERT ON temp1 FOR EACH ROW
    BEGIN
        INSERT into table2("value1", "value2", neededId);
    END;
    
END$$

DELIMITER ;

CodePudding user response：

SQL Statements Not Permitted in Stored Routines

Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. ... Exceptions are SIGNAL, RESIGNAL, and GET DIAGNOSTICS, which are not permissible as prepared statements but are permitted in stored programs.

SQL Syntax Permitted in Prepared Statements

CREATE TRIGGER is not listed.

Finally: the trigger cannot be created in stored procedure, function, prepared statement, trigger or event procedure.

CodePudding user response：

In MySQL, you can't create a trigger for a temporary table, regardless of whether you do it in a stored procedure or not.

https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html says:

The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

I assume the same is true of MariaDB. It's not clear from your question which one you use. You say MySQL at first, but you tagged the question mariadb. Be aware that these are not the same database product, and they are not necessarily compatible.

Here's a demo of the error, tested on MySQL 8.0.28:

mysql> create temporary table t ( i int );

mysql> create trigger t before insert on t for each row set NEW.i = 42;
ERROR 1361 (HY000): Trigger's 't' is view or temporary table

So in your case, you cannot use a trigger for the temporary table. You'll have to think of a different way to implement inserts to your second table.