mysql触发器
show triggers;
select * from information_schema.`TRIGGERS`;
select * from `performance_schema`.prepared_statements_instances;
DELIMITER $$
CREATE TRIGGER before_insert_user
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
-- 校验 age 是否大于 18
IF NEW.name <= 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age must be greater than 18';
END IF;
-- 校验 email 是否符合格式（简单示例）
IF NOT NEW.pwd REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email format';
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER before_insert_user_check_duplicate
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
-- 检查 username 是否已经存在
IF EXISTS (SELECT 1 FROM users WHERE username = NEW.username) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Username already exists';
END IF;
END$$
DELIMITER ;
sql
DELIMITER $$
CREATE TRIGGER before_insert_user_fill_timestamp
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
-- 如果没有提供 created_at，则自动填充当前时间
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END$$
DELIMITER ;
