创建一个触发器以在创建表时创建一个触发器 [英] Create a TRIGGER to create a TRIGGER when a table is created

问题描述

我有兴趣创建一个 TRIGGER ，当创建表时该 TRIGGER 。

I am interested in creating a TRIGGER that creates a TRIGGER when a table is created.

在mysql数据库中浏览时，我注意到返回的架构表为：

Poking around in mysql databases, I noticed that the tables for a schema are returned with:

从information_schema.TABLES中选择TABLE_NAME，TABLE_SCHEMA；

以下是创建 TRIGGER ，以创建绑定到其他模式的 TRIGGER ？

Is the following a proper method to create a TRIGGER, to create a TRIGGER bound to a different schema?

DELIMITER //
CREATE TRIGGER `information_schema`.`argus_table_creation`
    AFTER INSERT on `TABLES`
    BEGIN
        --trigger here
        CREATE TRIGGER `argus`.`after_argus_insert` --create trigger bound to DB 'argus', titled 'after_insert'
        AFTER INSERT ON `argus_table_2013_06_27` FOR EACH ROW -- perform the trigger, `after insert`, on the `argus_table_2013_06_27` table, `for each row` that is inserted.
        BEGIN
                INSERT INTO argus.historic_argus_saddrdaddr(saddr, daddr)
                (
                SELECT saddr, daddr
                FROM (SELECT NEW.saddr, NEW.daddr) as derived
                WHERE NOT EXISTS (SELECT saddr, daddr FROM argus.historic_argus_saddrdaddr where historic_argus_saddrdaddr.saddr = derived.saddr and historic_argus_saddrdaddr.daddr = derived.daddr)
                );
        END//
    END//
DELIMITER;

原因：

我使用客户端自动创建与给定命名方案匹配的新表的应用程序（ rasqlinsert ） （您可以在其中使用strftime（f））。我需要在这些自动生成的表上创建触发器。这就是为什么我要寻找解决方案。

Reasoning:

I use a client application (rasqlinsert) that automatically creates new tables that match a given naming scheme (where you can utilize strftime(f)). I need to create a trigger on these auto-generated tables as they are generated. This is why I am looking for a solution.

是否可以向开发人员提供建议软件使用类似于 CREATE TABLE n LIKE x 的命令，但是否复制触发器？

Is it possible to advise the developer of the software to use a command similar to CREATE TABLE n LIKE x, but have it copy triggers?

PS
与开发人员联系。将使用 3.0.7.11 进行更改，以接受随创建新数据库一起执行的任意SQL。

P.S. Contacted dev. Will roll change in with 3.0.7.11 to accept arbitrary SQL to perform along with creation of new DB. Expects code to hit dev repo by Monday.

推荐答案

您不能在 information_schema 数据库。

能否提供引用？ – mbrownnyc

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

MySQL 5.1参考手册

第20章，INFORMATION_SCHEMA表

INFORMATION_SCHEMA数据库的使用说明

INFORMATION_SCHEMA是每个MySQL实例中的一个数据库，该位置存储有关MySQL服务器维护的所有其他数据库的信息。 INFORMATION_SCHEMA数据库包含几个只读表。它们实际上是视图，而不是基表，因此没有与之关联的文件，并且您无法在它们上设置触发器。

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them.

这篇关于创建一个触发器以在创建表时创建一个触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！