Question 174270 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL How to inside a Trigger, Select Multiple rows and then insert them

I am creating a "log" system and for this, trigger would be the best way to go I think. The thing is I have a header table (table1) and a line item table (table2). I created 2 new tables (table1log) and (table2log) they are copies of the non-log table. I want that when I update a row in the header table (table1), it will store the current row values in the log table (table1log), but I also want to store every row with the same header_id from (table2) into (table2log). So I created a BEFORE UPDATE trigger, the part to save the updated row in (table1) is working, but when it comes to saving (table2) rows, it don’t work. It says I can’t return a result set from trigger nor I can’t create a view.

I tried to use a plain select and insert, I was expecting it to work, but It returned this
Error 1415: Not allowed to return a result set from a trigger SQL Statement

I also tried to create a view inside the trigger and then simply get my data from the view, but it returned this
Error 1422: Explicit or implicit commit is not allowed in stored function or trigger SQL Statement

I’ve tried everything that is talked in these subjects, but none seems to be exactly what I want.

CREATE DEFINER='root'@'%' TRIGGER 'dev'.'table1_BEFORE_UPDATE' BEFORE UPDATE ON "table1" FOR EACH ROW
BEGIN
    INSERT INTO dev.table1log
      (NumHeader, Name)
      VALUES (OLD.NumHeader, OLD.Name);
    
    SELECT * INTO @table2v FROM dev.table2 WHERE NumHeader = OLD.NumHeader;
    
    INSERT INTO dev.table2log
      (NumHeader, Name)
      VALUES (@table2v.NumHeader, @table2v.Name);
END

I am looking to do something like this, can anyone enlighten me on how I should do it ?

Tags:

2

Answers


  1. 0

    It´s not possible directly to manage it via triggers.
    I would create a stored procedure first which updates the tables you´ve mentioned and with this created stored procedure you can call a BEFORE UPDATE trigger on the table.

    Link to the Basics of Stored Procedures: Basics

    If you need additional help with the code, please let me know.

    Login or Signup to reply.
  2. 0

    You can use MySQL a variable only for a single scalar, not a result set.

    But you don’t need any variables in your case, because you can use INSERT INTO … SELECT.

    CREATE DEFINER='root'@'%' TRIGGER dev.table1_BEFORE_UPDATE AFTER UPDATE ON table1 FOR EACH ROW
BEGIN
    INSERT INTO dev.table1log
      (NumHeader, Name)
      VALUES (OLD.NumHeader, OLD.Name);
    
    INSERT INTO dev.table2log
      (NumHeader, Name)
      SELECT NumHeader, Name
      FROM dev.table2
      WHERE NumHeader = OLD.NumHeader;
END

    I also edited to change this to an AFTER trigger, because you want it to run after the UPDATE that spawned the trigger is successful. If you do the inserts in a BEFORE trigger, but the UPDATE fails, then it would have to roll back more changes.

    I also fixed the incorrect use of quote marks.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search