Mysql触发器

DROP TRIGGER IF EXISTS triggerName;
DELIMITER $
create trigger triggerName
after/before insert/update/delete on 表名
for each row   #这句话在mysql是固定的
begin
    sql语句;
end $
DELIMITER;

 

-- 创建表table1
DROP TABLE IF EXISTS table1;
CREATE TABLE table1(
    table1_id VARCHAR(10)
);
-- 创建表table2
DROP TABLE IF EXISTS table2;
CREATE TABLE table2(
    table2_id VARCHAR(10)
);
-- 创建触发器:insert_trigger_table1,增加table1表记录后自动将记录增加到table2表中
DROP TRIGGER IF EXISTS insert_trigger_table1;
DELIMITER $
CREATE TRIGGER insert_trigger_table1 
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
INSERT INTO table2(table2_id) VALUE(new.table1_id);
END$
DELIMITER;

-- 测试 
INSERT INTO table1(table1_id) VALUES('110');
SELECT * FROM table1;
SELECT * FROM table2;
-- 创建触发器:delete_trigger_table1,删除table1表记录后自动将table2表中对应的记录删去
DROP TRIGGER IF EXISTS delete_trigger_table1;
DELIMITER $
CREATE TRIGGER delete_trigger_table1
AFTER DELETE ON table1
FOR EACH ROW
BEGIN
      DELETE FROM table2 WHERE table2_id=old.table1_id;
END$
DELIMITER;

-- 测试 
DELETE FROM table1 WHERE table1_id='110';
SELECT * FROM table1;
SELECT * FROM table2;

• 感谢您的阅读。如果感觉文章对您有用，麻烦您动动手指点个赞，以资鼓励。谢谢！