5

I have created this trigger to insert a value calculated value to a field in the table in case the user forget to put the data himself:

DELIMITER //
CREATE TRIGGER OnNewTableRegistry BEFORE INSERT ON eduardo8_plataforma.tabela
FOR EACH ROW
BEGIN
    IF NEW.ut = null THEN
        SET NEW.ut = GetUT('tabela');
    ELSEIF NEW.ut = '' THEN
        SET NEW.ut = GetUT('tabela');
    END IF;
END;
//
DELIMITER ;

But I need to do the same with every table in that database. Is it possible to use the same trigger to all the tables and if YES, how do we get the name of the table that triggered to use it in the line 6 and 8 where there is tabela specified?

I need something like this:

DELIMITER //
CREATE TRIGGER OnNewTableRegistry BEFORE INSERT ON (* as _TableName)
FOR EACH ROW
BEGIN
    IF NEW._TableName.ut = null THEN
        SET NEW._TableName.ut = GetUT(_TableName);
    ELSEIF NEW._TableName.ut = '' THEN
        SET NEW._TableName.ut = GetUT(_TableName);
    END IF;
END;
//
DELIMITER ;
NaN
  • 8,596
  • 20
  • 79
  • 153
  • Just so it's clear do you want one trigger to update multiple tables in a database or do you want to re-use the same trigger on several tables in the database? – dethtron5000 Jul 02 '13 at 22:58
  • I need to reuse the same trigger in any table in the database ;-) – NaN Jul 02 '13 at 23:46

1 Answers1

3

No. The syntax doesn't provide for it.

It makes no sense to allow it, because the NEW keyword must refer to a particular row definition. If you have two tables with the same row definition, they should be made into the one table, with another column denoting the difference.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Hi Bohemian, what if all of my tables from a particular database have a column created_at DATETIME and I want to set them as NOW() while inserting? Can I have some schema-level trigger to achieve this? – exAres Feb 14 '14 at 12:04
  • @user2745266 You don't need a trigger. Standard sql will suffice. Define the column as `created_at datetime not null default now()` and omit the column from the insert statement `insert into mytable (...) values (...)` (listing all *other* columns within the first set of brackets), and the current time will get inserted automatically. – Bohemian Feb 14 '14 at 15:40
  • Hi, thanks a lot for reply. But default now() gives me an error : "Invalid default value for 'created_at'".... – exAres Feb 17 '14 at 11:23
  • @user2745266 What is the full definition of the column you are trying? – Bohemian Feb 17 '14 at 11:34
  • Hi. It is : `CREATE TABLE IF NOT EXISTS `database`.`users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL, `password` VARCHAR(255) NULL, `salt` VARCHAR(255) NULL, `is_admin` BIT(1) NULL, `created_at` DATETIME NOT NULL DEFAULT now(), `updated_at` DATETIME NULL, PRIMARY KEY (`id`), INDEX `id` (`id` ASC)) ENGINE = InnoDB;` – exAres Feb 17 '14 at 11:39
  • I think that functions like now() can not be used directly in column definition(unlike CURRENT_TIMESTAMP for timestamp column). The only way I see is to use trigger now. Please help me out if I am wrong. – exAres Feb 17 '14 at 11:40
  • 1
    @user2745266 I've checked a few places and you're right: can't use functions for default values. A trigger is the only way. Good luck. – Bohemian Feb 17 '14 at 11:56