mysql 触发器示例

示例1:

DELIMITER $$
USE `数据库名称`$$
DROP TRIGGER /*!50032 IF EXISTS */ `触发器名称`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
  TRIGGER `触发器名称` AFTER INSERT ON `表名称` 
FOR EACH ROW BEGIN
  -- INSERT INTO `表名称` VALUES (new.id,.....);
  INSERT INTO 表名称(HostUserID,TeamName,CanDelete) VALUES(new.Id,....);
END;
$$
DELIMITER ;

 

示例2:

DELIMITER $$
USE `数据库名称`$$
DROP TRIGGER /*!50032 IF EXISTS */ `触发器名称`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `触发器名称` AFTER INSERT ON `表名称`

FOR EACH ROW
BEGIN

DECLARE last_time DATETIME;

DECLARE user_type INT;
DECLARE service_type INT;
SET last_time := (SELECT .....) ;

SET user_type := (SELECT .....) ;

SET service_type := (SELECT services_type.....) ;

IF user_type <> x THEN
UPDATE Onnetuser SET .... ;

ELSEIF user_type = y THEN

UPDATE Onnetuser SET ... ;

ELSEIF user_type = d THEN

IF service_type = t THEN

UPDATE Onnetuser SET O...;

ELSEIF service_type > h THEN

IF new.`type_id` = 1 OR new.`type_id` = 2 OR new.`type_id` = 3 THEN

UPDATE xxx SET ... ;

END IF;

END IF;

END IF;

END;
$$

DELIMITER ;