例如,创建触发器:
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER test.t_afterinsert_on_test AFTER INSERT
ON test.t_user
FOR EACH ROW BEGIN
INSERT INTO test2.t_user VALUES(new.id, new.name, new.password);
END $$
DELIMITER ;在执行INSERT INTO test2.t_user VALUES(new.id, new.name, new.password);之前需要进行判断id是否存在(类似于:if(select * from t_user where id = new.id) == 0,之类的判断),不存在,则执行,因为是两个表。如何实现?求解,谢谢
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER test.t_afterinsert_on_test AFTER INSERT
ON test.t_user
FOR EACH ROW BEGIN
INSERT INTO test2.t_user VALUES(new.id, new.name, new.password);
END $$
DELIMITER ;在执行INSERT INTO test2.t_user VALUES(new.id, new.name, new.password);之前需要进行判断id是否存在(类似于:if(select * from t_user where id = new.id) == 0,之类的判断),不存在,则执行,因为是两个表。如何实现?求解,谢谢
if not exists (select * from t_user where id = new.id) then
INSERT INTO test2.t_user VALUES(new.id, new.name, new.password);
end if;
END $$
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER test.t_afterinsert_on_test AFTER INSERT
ON test.t_user
INSERT INTO test2.t_user VALUES(new.id, new.name, new.password) on duplicate update name=new.name;