我对创建一个TRIGGER(在创建表时创建TRIGGER)感兴趣.
在mysql数据库中闲逛,我注意到返回的架构表如下:
从information_schema.TABLES中选择TABLE_NAME,TABLE_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)).我需要在这些自动生成的表上创建触发器.这就是为什么我在寻找解决方案.
另一种可能的解决方案
是否可以建议软件开发人员使用类似于CREATE TABLE n LIKE x的命令,但要复制触发器?
附言
与开发人员联系.将在3.0.7.11中进行更改以接受任意SQL与新数据库的创建一起执行.预计代码将在周一发布.
解决方法:
您不能在information_schema数据库中的表上设置触发器.
能否请您提供引用? – mbrownnyc
http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
MySQL 5.1 Reference Manual
Chapter 20. INFORMATION_SCHEMA Tables
Usage Notes for the INFORMATION_SCHEMA Database
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.