Is it possible to simplify the following code in a trigger?

     EXECUTE IMMEDIATE 'SELECT SE.ProcessingOrder INTO ? FROM products PR INNER JOIN sections SE ON SE.sectionid=PR.sectionid WHERE PR.productid=?' USING ProcessingOrder,ProductID;
     EXECUTE IMMEDIATE 'SELECT SE.SectionID INTO ? FROM products PR INNER JOIN sections SE ON SE.sectionid=PR.sectionid WHERE PR.productid=?' USING SectionID,ProductID;
  END IF;

to

    EXECUTE IMMEDIATE 'SELECT SE.ProcessingOrder INTO ?,SE.SectionID INTO ? FROM products PR INNER JOIN sections SE ON SE.sectionid=PR.sectionid WHERE PR.productid=?' USING ProcessingOrder,SectionID,ProductID;

I tried this already but the trigger didn't execute after changing,

Mike


I'm slightly baffled here. You say the trigger didn't execute. If so that's the problem not your code. If the trigger executes but doesn't do what you think it should do you get an error, if so what?

Have you tested the SQL at the core of the EXECUTE IMMEDIATE to see if it actually produces what you want?

SOrry I can't be more help, to sort these things out I generally need table catalog & data - others can operate from theory but I can't

Roy Lambert

Hi Roy,

I have to correct it. The trigger was executed.

The following message appeared.
"ElevateDB Error #700 An error was found in the statement at line 11 and column 22 (Invalid expression . found, table qualifier not allowed)"

However after changing it to the following statement it was working.

"EXECUTE IMMEDIATE 'SELECT SE.ProcessingOrder, SE.SectionID INTO ?,? FROM products PR INNER JOIN sections SE ON SE.sectionid=PR.sectionid WHERE PR.productid=?' USING ProcessingOrder,SectionID,ProductID;"

Thanks for your time!