MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 6412|回复: 1

[触发器及事件] 一个简单的触发器的例子

[复制链接]
发表于 2010-8-16 17:15:36 | 显示全部楼层 |阅读模式
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
 楼主| 发表于 2010-8-23 13:57:38 | 显示全部楼层
触发器语法:

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
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-29 07:21 , Processed in 0.066896 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表