mysql 触发器创建实例（数据查询与备份触发器）（mysql 触发器中止）

before insert触发器

DROP TRIGGER `before_insert_backup_password`;

CREATE DEFINER=`root`@`%` TRIGGER `before_insert_backup_password` BEFORE INSERT ON `osspub_manager_devicefirmwares_password_copy`
FOR EACH ROW 
BEGIN
set @trigger_key = null; # 当查询为空时，select...into不会对变量赋值
select key into @trigger_key from `osspub_manager_computers` where devicefirmwares_id = NEW.device_id;
if @trigger_key IS NOT NULL then
    insert into osspub_manager_bios_trig_passwords values (null, NEW.newap, concat('new', NEW.type), @trigger_key , 'insert', now());
else 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '未找到计算机，操作已终止';
end if;
END;

before update触发器

DROP TRIGGER `before_update_backup_password`;

CREATE DEFINER=`root`@`%` TRIGGER `before_update_backup_password` BEFORE UPDATE ON `osspub_manager_devicefirmwares_password_copy`
FOR EACH ROW 
BEGIN
set @trigger_key = (select key from `osspub_manager_computers` where devicefirmwares_id = NEW.device_id); # 根据select返回直接进行复制（包括返回null）
if @trigger_key IS NOT NULL then
    if NEW.newap <> OLD.newap then
        insert into osspub_manager_bios_trig_passwords values (null, NEW.newap, concat('new', NEW.type), @trigger_key , 'update', now());
    end if;
else 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '未找到计算机，操作已终止';
end if;
END;

before delete触发器

DROP TRIGGER `before_delete_backup_password`;

CREATE DEFINER=`root`@`%` TRIGGER `before_delete_backup_password` BEFORE DELETE ON `osspub_manager_devicefirmwares_password_copy`
FOR EACH ROW 
BEGIN
declare trigger_key varchar(100); # 局部变量，单次有效
select key into trigger_key from `osspub_manager_computers` where devicefirmwares_id = OLD.device_id;
if trigger_key IS NOT NULL then
    insert into osspub_manager_bios_trig_passwords values (null, OLD.newap, concat('new', OLD.type), trigger_key, 'delete', now());
else 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '未找到计算机，操作已终止';
end if;
END;

参考链接：
官方文档：中止BEGIN…END
mysql触发器教程