1.查找触发器
SELECT
TRIGGER_SCHEMA, -- 数据库名
TRIGGER_NAME, -- 触发器名称
EVENT_MANIPULATION, -- 触发事件(INSERT, UPDATE, DELETE)
EVENT_OBJECT_TABLE, -- 触发器关联的表名
ACTION_TIMING, -- 触发时机(BEFORE, AFTER)
ACTION_STATEMENT -- 触发器的SQL语句
FROM
INFORMATION_SCHEMA.TRIGGERS
WHERE
TRIGGER_SCHEMA = 'your_database_name'; -- 替换为你的数据库名
2.新增触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body;
说明:
trigger_name 是触发器的名称。
trigger_time 是触发器的动作时间,可以是 BEFORE 或 AFTER。
trigger_event 是触发器的事件类型,可以是 INSERT, UPDATE, 或 DELETE。
table_name 是触发器要关联的表名。
FOR EACH ROW 表示对于每一行的操作都会触发该触发器。
trigger_body 是触发器要执行的操作
示例:
1.新增
CREATE TRIGGER trigger_on_special_insert AFTER INSERT
ON mam_special FOR EACH ROW
INSERT INTO mam_asset_trigger_task(asset_id, asset_type, operate,create_time) VALUES(NEW.special_id,8,'insert',now());
2.修改
CREATE TRIGGER trigger_on_special_update AFTER UPDATE
ON mam_special FOR EACH ROW
INSERT INTO mam_asset_trigger_task(asset_id, asset_type, operate,create_time) VALUES(NEW.special_id,8,'insert',now());
3.删除
CREATE TRIGGER trigger_on_special_delete AFTER DELETE
ON mam_special FOR EACH ROW
INSERT INTO mam_asset_trigger_task(asset_id, asset_type, operate,create_time) VALUES(old.special_id,8,'delete',now());
3.删除触发器
DROP TRIGGER trigger_name;-- 替换为你的触发器名称
Mysql触发器使用说明
最新推荐文章于 2024-08-11 15:20:18 发布