-- 两表双向增加触发器
delimiter //
-- DROP TRIGGER IF EXISTS admin_insert
CREATE TRIGGER admin_insert AFTER INSERT ON hotmine.admin FOR EACH ROW
BEGIN
IF NOT EXISTS ( SELECT * FROM hotmine_new.admin WHERE hotmine_new.admin.id = new.id ) THEN
INSERT INTO hotmine_new.admin ( hotmine_new.admin.`user`, hotmine_new.admin.`password` )
VALUES
(new.`user`,new.`password`);
END IF;
END//
delimiter;
delimiter //
CREATE TRIGGER admin_new_insert AFTER INSERT ON hotmine_new.admin FOR EACH ROW
BEGIN
IF NOT EXISTS ( SELECT * FROM hotmine.admin WHERE hotmine.admin.id = new.id ) THEN
INSERT INTO hotmine.admin ( hotmine.admin.`user`, hotmine.admin.`password` )
VALUES
(new.`user`,new.`password`);
END IF;
END//
delimiter;
-- 两表双向删除触发器CREATE TRIGGER admin_del AFTER DELETE ON hotmine.admin FOR EACH ROW
BEGIN
IF
EXISTS ( SELECT * FROM hotmine_new.admin WHERE hotmine_new.admin.id = old.id ) THEN
DELETE
FROM
hotmine_new.admin
WHERE
hotmine_new.admin.id = OLD.id;
END IF;
END;
CREATE TRIGGER admin_new_del AFTER DELETE ON hotmine_new.admin FOR EACH ROW
BEGIN
IF
EXISTS ( SELECT * FROM hotmine.admin WHERE hotmine.admin.id = old.id ) THEN
DELETE
FROM
hotmine.admin
WHERE
hotmine.admin.id = OLD.id;
END IF;
END;
-- 两表双向更新触发器
delimiter //
CREATE TRIGGER admin_update AFTER UPDATE ON hotmine.admin FOR EACH ROW
BEGIN
IF
NOT EXISTS (
SELECT
*
FROM
hotmine_new.admin
WHERE
hotmine_new.admin.id = old.id
AND hotmine_new.admin.`user` = new.`user`
AND hotmine_new.admin.`password` = new.`password`
) THEN
UPDATE hotmine_new.admin
SET hotmine_new.admin.`user` = new.`user`,
hotmine_new.admin.`password` = new.`password`
WHERE
hotmine_new.admin.id = old.id;
END IF;
END//
delimiter;
delimiter //
CREATE TRIGGER admin_new_update AFTER UPDATE ON hotmine_new.admin FOR EACH ROW
BEGIN
IF
NOT EXISTS (
SELECT
*
FROM
hotmine.admin
WHERE
hotmine.admin.id = old.id
AND hotmine.admin.`user` = new.`user`
AND hotmine.admin.`password` = new.`password`
) THEN
UPDATE hotmine.admin
SET hotmine.admin.`user` = new.`user`,
hotmine.admin.`password` = new.`password`
WHERE
hotmine.admin.id = old.id;
END IF;
END//
delimiter;
mysql的不同数据库的两表双向触发器
最新推荐文章于 2022-05-14 16:07:38 发布