0

This is my Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_count`(IN `col_name` VARCHAR(15))
BEGIN
SET @col = CONCAT("pass_",SUBSTRING(col_name,5,3));
SET @row = SUBSTRING(col_name,1,4);
SET @query = CONCAT("UPDATE stk_center_temp SET ",@col," = ",@col,"-1 WHERE center = '",@row,"'");
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

This is my trigger

CREATE TRIGGER `up_count` AFTER INSERT ON `admdamageid`
FOR EACH ROW CALL update_count("KATRSTD")

when I call stored procedure from my trigger it shows me an error

1336 - Dynamic SQL is not allowed in stored function or trigger

when i call my stored procedure directly it execute

I Recently tried this trigger

CREATE TRIGGER `up_count` AFTER INSERT ON `table1`
FOR EACH ROW BEGIN
DECLARE col_name VARCHAR(10);
DECLARE col VARCHAR(50);
DECLARE row_cl VARCHAR(50);
SET col_name = NEW.id_no;
SET col = CONCAT("pass_",SUBSTRING(col_name,5,3));
SET row_cl = SUBSTRING(col_name,1,4);
UPDATE table2
SET col = col-1 WHERE center = row_cl;
END

BUT when i insert row in table1 i got error

1054 - Unknown column 'col' in 'field list'

Community
  • 1
  • 1
ashwini
  • 1
  • 4
  • 1
    That's true and there is no way around it. – P.Salmon Nov 10 '17 at 05:52
  • Most of your trigger code is commented out so it's not clear what you are doing ,either add the acutal code to the question as text or to sql fiddle. – P.Salmon Nov 10 '17 at 07:30
  • ok sir just check above recently code of trigger – ashwini Nov 10 '17 at 07:44
  • Makes no sense at all. There is no reference at all to `admdamageid`, I would expect to see a reference to new. something you haven't tackled the update issue and your question doesn't describe what you are trying to achieve. Please add table definitions and sample data as text to the question (or sqlfiddle) together with expected result. – P.Salmon Nov 10 '17 at 07:55
  • sir please see recently added trigger – ashwini Nov 10 '17 at 08:17

0 Answers0