How to make a trigger that fires after update only for tables that begin with a "Table_"

Question

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?

Answer 1

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.

Answer 2

A trigger belongs to a specific table. You need to manually add a trigger to each table you want monitored (in your case, each table whose name starts with 'A').

Documentation: CREATE TRIGGER Syntax

Your requirements seem weird to me. It's quite unusual to update data only for tables whose name matches a filter. Are you sure you can't reorganize your database structure and have the data for all "A*" tables merged into a single table?