MariaDB社区

标题: 一个简单的触发器的例子 [打印本页]

作者: kider 时间: 2010-8-16 17:15
标题: 一个简单的触发器的例子
CREATE TABLE ` tb_kider` (
  `Id` INT(11) NOT NULL
) ENGINE=INNODB  ;

/* 创建触发器：当表tb_kider_org字段Id更新时，触发一条记录到另一张表tb_kider中。  */
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `TRI_tb_kider_UpdateId`$$
CREATE
/*!50017 DEFINER = 'admin'@'%' */
TRIGGER ` TRI_tb_kider_UpdateId` AFTER UPDATE ON `UP_tb_org`
FOR EACH ROW BEGIN
   IF(NEW.Id IS NOT NULL ) THEN
      INSERT INTO UP_tb_kider(Id)
      VALUES(NEW.Id);
   END IF;
END;
$$
DELIMITER ;

例子2：

不同库的表

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `ha`.`tr_ttt`$$

CREATE
/*!50017 DEFINER = 'root'@'%' */

TRIGGER `tr_ttt` BEFORE INSERT ON ha.`content`

FOR EACH ROW BEGIN

IF ( new.a IS NOT NULL ) THEN

INSERT INTO test.a(a) VALUES(new.a);

END IF;

END;

DELIMITER ;

#Added to allow create function

log_bin_trust_function_creators = 1

作者: kider 时间: 2010-8-23 13:57
触发器语法：

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

This statement creates a new trigger. A trigger is a named database
object that is associated with a table, and that activates when a
particular event occurs for the table. The trigger becomes associated
with the table named tbl_name, which must refer to a permanent table.
You cannot associate a trigger with a TEMPORARY table or a view.

CREATE TRIGGER requires the TRIGGER privilege for the table associated
with the trigger. (Before MySQL 5.1.6, this statement requires the
SUPER privilege.)

The DEFINER clause determines the security context to be used when
checking access privileges at trigger activation time.

trigger_time is the trigger action time. It can be BEFORE or AFTER to
indicate that the trigger activates before or after each row to be
modified.

trigger_event indicates the kind of statement that activates the
trigger. The trigger_event can be one of the following:

o INSERT: The trigger is activated whenever a new row is inserted into
  the table; for example, through INSERT, LOAD DATA, and REPLACE
  statements.

o UPDATE: The trigger is activated whenever a row is modified; for
  example, through UPDATE statements.

o DELETE: The trigger is activated whenever a row is deleted from the
  table; for example, through DELETE and REPLACE statements. However,
  DROP TABLE and TRUNCATE statements on the table do not activate this
  trigger, because they do not use DELETE. Dropping a partition does
  not activate DELETE triggers, either. See [HELP TRUNCATE TABLE].

更多详细解释，参考官方URL: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

欢迎光临 MariaDB社区 (http://123.56.88.72/)