How to make a trigger that fires after the update only for tables starting with & ldquo; Table_ & rdquo;

advertisements

I am trying to make a trigger that fires under certain conditions conditions AFTER a UPDATE , on tables that start with a specific string like "A_" for example.

CREATE TABLE `Table_TEST` (
    `id` INT(11) NULL DEFAULT NULL,
    `A` INT(11) NULL DEFAULT NULL,
    `B` INT(11) NULL DEFAULT NULL,
    `C` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

The trigger should make the SUM (column "A") minus SUM(column "B") up to the index of the row that was updated and write this SUM into column "C" in this row.

CREATE DEFINER=`root`@`localhost`
TRIGGER `Table_TEST_before_insert` BEFORE UPDATE ON `Table_TEST` FOR EACH ROW
BEGIN
SET new.C = (SUM(new.A)-SUM(new.B));
END

This Trigger does not work and brings this failure

UPDATE `Testdb`.`Table_TEST` SET `b`='4' WHERE  `id`=1 LIMIT 1;
/* SQL Fehler (1111): Invalid use of group function */

After this it should update all other values in column "C" that come after that index according the given formula up to that respective index.

Right now I written a function in php that I append after each update in the database in my php script. But its in fact not wat I want.

I want this as a Routine/Trigger.

What I tried looks like this, but be aware that its not working

CREATE DEFINER=`root`@`localhost`
TRIGGER `TABLE_1_before_update` BEFORE UPDATE ON `TABLE_1` FOR EACH ROW
BEGIN
SET new.C = (SUM(new.A)-SUM(new.B)) WHERE id=NEW.id;
END

The Failure I get is : SQL Fehler (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=NEW.id; END' at line 2

Can you please help me?


Well, I think you can select the tables and then you do your trigger.

SELECT `TABLE_NAME`
  FROM information_schema.tables
 WHERE table_schema='<your-database-name>'
   AND `TABLE_NAME` LIKE 'a%'

EDIT:

Not tested, but this could also work (if not, just select the table names with the previous query and then create each triggers as I said before).

CREATE
DEFINER=`user`@`localhost`
TRIGGER `trigger_name` AFTER UPDATE
ON (
    SELECT `TABLE_NAME`
      FROM information_schema.tables
    WHERE table_schema='<your-database-name>'
      AND `TABLE_NAME` LIKE 'a%'
)  FOR EACH ROW 

BEGIN
    SET new.C = (SUM(new.A)-SUM(new.B)) WHERE id=NEW.id;
END

Note: Make sure to replace <your-database-name> for your database name.