mysql客户端安装后如何设置触发器_mysql客户端触发器创建与使用方法

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

DELIMITER $$
CREATE TRIGGER set_create_time
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END$$
DELIMITER ;

-- 假设有一个日志表 user_log
CREATE TABLE user_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(10),
    changed_at DATETIME
);
<p>DELIMITER $$
CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_log (user_id, action, changed_at)
VALUES (OLD.id, 'UPDATE', NOW());
END$$
DELIMITER ;</p>

DELIMITER $$
CREATE TRIGGER prevent_admin_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    IF OLD.username = 'admin' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止删除管理员账户';
    END IF;
END$$
DELIMITER ;

SHOW TRIGGERS;

SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_NAME = 'trigger_name';

DROP TRIGGER IF EXISTS trigger_name;