mysql trigger运用笔记

mysql trigger使用笔记

trigger语法:

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

例如:

DELIMITER $$

USE `gjp`$$

CREATE

DEFINER=`root`@`localhost`

TRIGGER `gjp`.`end_it`

BEFORE UPDATE ON `gjp`.`gjp$abc`

FOR EACH ROW

BEGIN

if NEW.time=0 then

    set NEW.end_time=current_timestamp;

end if;

END$$

需要注意的是如果trigger由一个update触发而执行的update语句,需要用set,而不能直接用update语句,如上面那个trigger的if NEW.time=0 then set NEW.end_time=current_timestamp不能写成update gjp$abc set gjp$abc.end_time = current_timestamp where gjp$abc.time=0;否则会形成循环而报Can’t update table ‘gjp$abc’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

还需注意这里的trigger_time需是before.

mysql trigger运用笔记

相关文章:

你感兴趣的文章:

标签云: