Trigger names exist in the schema namespace,meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
因为触发器在单表的命名空间内，所以同一个表的触发器名称需要不同。不同表可以有相同的触发器名称。
3. 触发器执行顺序
如果有相同的update（或者delete,insert）触发器，就会按照创建的时间来执行。
而FOLLOWS 和 PRECEDES 可以修改trigger的执行顺序
例如官方的案例：
MysqL> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
--建立测试表CREATE TABLE `triggletest_triggle` (
`id` INT(11) NOT NULL,`name` VARCHAR(5) NULL DEFAULT NULL,PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
--建立目标表
CREATE TABLE `triggletest` (
`seq` INT(11) NOT NULL,`id` INT(11) NOT NULL,PRIMARY KEY (`seq`),INDEX `id` (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
---写入测试数据
INSERT INTO `triggletest_triggle` VALUES(1,'A');
--建立触发器
DROP TRIGGER if EXISTS test1
CREATE TRIGGER test1
AFTER UPDATE ON triggletest_triggle
FOR EACH ROW
BEGIN
INSERT INTO triggletest(id) values (new.id);
END
执行触发器语句,报错,报错内容如下： MysqL实例
/* sql错误(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 'CREATE TRIGGER test1
AFTER UPDATE ON triggletest_triggle
FOR EACH ROW
BEGIN ' at line 2 */
---最终实现代码
DROP TRIGGER if EXISTS test1;
CREATE TRIGGER test1 AFTER UPDATE ON test.triggletest_triggle FOR EACH ROW
BEGIN
INSERT INTO triggletest(id,name) values (new.id,new.name);
END;
mysql> create trigger t2 -> after -> insert on ord -> for each row -> begin update goods set num=num-new.mch where gid = new.gid; -> end$ Query OK, 0 rows affected (0.02 sec)
mysql> 查看已有触发器trigger -> ^C mysql> show tigger;
删除：
mysql> create trigger t3 after delete on ord -> for each row begin update goods set num = num -old.mch where -> gid = old.gid;end $ Query OK, 0 rows affected (0.02 sec)
mysql> show triggers; -> $ +---------+--------+-------+-------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +---------+--------+-------+-------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | t2 | INSERT | ord | begin update goods set num=num-new.mch where gid = new.gid; end | AFTER | 2019-06-03 07:47:00.22 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | | t3 | DELETE | ord | begin update goods set num = num -old.mch where gid = old.gid;end | AFTER | 2019-06-03 07:57:21.92 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | +---------+--------+-------+-------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.00 sec)
mysql触发器之创建使用触发器简单示例
本文实例讲述了mysql触发器之创建使用触发器。分享给大家供大家参考，具体如下：
我们可以可以使用CREATE TRIGGER语句创建一个新的触发器，来看下具体的语法：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END;
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit
SET action = ''update'',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END$$
DELIMITER ;
