MySQL触发器可以关联到多个表还是所有表? [英] Can a MySQL trigger be associated to more than one table or by all tables?

问题描述

我创建了此触发器，以将计算得出的值插入表中的字段，以防用户忘记自己放置数据:

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 ;

但是我需要对该数据库中的每个表执行相同的操作.是否可以对所有表使用相同的触发器，如果​​是，那么如何在指定了tabela的第6行和第8行中获取触发使用该表的表的名称?

我需要这样的东西:

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 ;

解决方案

否. 语法没有提供. >

不允许这样做是有道理的，因为NEW关键字必须引用特定的行定义.如果您有两个表具有相同的行定义，则应将它们合并为一个表，另一列表示差异.

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 ;

解决方案

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.

这篇关于MySQL触发器可以关联到多个表还是所有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！