/ 中存储网

MySQL存储过程和触发器的使用

2014-07-13 15:53:19 来源:中存储网

不做介绍了,我们先来看一下测试表结构:

CREATE TABLE `a` (    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,    `cnt` INT(11) NULL DEFAULT NULL,    PRIMARY KEY (`id`))COLLATE='utf8_general_ci'ENGINE=MyISAMROW_FORMAT=DEFAULT和存储过程相关的几个权限:

  alter routine,create routine,execute   和触发器相关的权限:

  trigger  创建一个测试账号:

grant select,update,insert,create,delete,trigger,alter routine,create routine,execute on test.* to 'mysqldba'@'%' identified by '123456';使用相关:
1.存储过程有定义者和调用者之说,触发器木有

我们可以为一个存储过程指明定义者和调用者,如果木有DEFINER,那默认就是创建者的环境,但是触发器不行,一旦创建了触发器,所有的用户执行的操作都会触发

例如:

DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`p_test`$$CREATE DEFINER=`mysqldba`@`%` PROCEDURE `p_test`(in a int)BEGIN        select a;END$$DELIMITER ;只让mysqldba账号调用,但是如果具有更大权限的账号,如:root,也是可以调用的

2.触发器可以调用存储过程

3.触发器可以访问它自己表的新旧数据(通过old.colname,new.colname访问),也可以影响其他表,但是不允许修改已经被函数或者触发器使用(读或者写)的表,在MySQL5.0.10之前的版本,触发器不能修改其他表

4.MySQL5.0的版本,所有的触发器都是基于行(FOR EACH ROW)的,因为触发器通过insert,update,delete激活,MySQL5.0不支持FOR EACH STATEMENT,如:

DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `test`.`tai_a`$$CREATE    /*!50017 DEFINER = 'mysqldba'@'%' */    TRIGGER `tai_a` AFTER INSERT ON `a`     FOR EACH ROW  -- MySQL 5.0只能这样,不能FOR EACH STATEMENT    BEGIN         insert into b values(NEW.id,NEW.cnt);                END;$$DELIMITER ;5.触发器可以通过UDF调用外部应用程序

详细可以参考:http://forge.mysql.com/projects/project.php?id=211

内部相关:
6.我们可以通过下面方式查看一个库下面的所有触发器:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENTFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='dbname';7.触发器的存储形式

目前触发器存放在.TRG文件中,每一个表中的触发器存储在一个TRG文件中,我们可以到表的数据目录下,看到这个文件,如:

[root@sunss test]# lsa.frm  a.MYD  a.MYI  a.TRG  b.frm  b.MYD  b.MYI  tai_a.TRN  tau_a.TRN  test.frm  test.MYD  test.MYI[root@sunss test]# pwd/home/mysql/test[root@sunss test]# cat a.TRG TYPE=TRIGGERStriggers='CREATE DEFINER=`mysqldba`@`%` TRIGGER `tau_a` AFTER UPDATE ON `a` n    FOR EACH ROW BEGINn            update b set cnt=NEW.cnt where id=NEW.id;n    END' 'CREATE DEFINER=`mysqldba`@`%` TRIGGER `tai_a` AFTER INSERT ON `a` n    FOR EACH ROW BEGINn         insert into b values(NEW.id,NEW.cnt);            n    END'sql_modes=0 0definers='mysqldba@%' 'mysqldba@%'client_cs_names='utf8' 'utf8'connection_cl_names='utf8_general_ci' 'utf8_general_ci'db_cl_names='utf8_general_ci' 'utf8_general_ci'You have new mail in /var/spool/mail/root[root@sunss test]#8.触发器可以更新远程的表,但远程的表必须使用FEDERATED存储引擎

复制相关:
9.触发器在MySQL复制环境中也是可以工作的

在MySQL5.0的版本和其他大多数数据库系统一样,触发器和复制都能工作。在主库上,通过触发器承载的操作,不会复制到从库上。但是,如果主库上存在的触发器,如果在从库上的相关表中也创建的话,在从库上的触发器就能和主库一样被激活、触发。

10.在主库上的操作是怎样被复制到从库上去的?

首先要确定一件事:主从库都有同样的触发器,在主库上创建的触发器也要在从库上重新创建一遍。这样DML语句在复制中就能在从库上激活触发器。例如:还是a表,我们在a表中创建了一个after insert 触发器,复制过程如下:

1).一条insert语句插入a表

2).after insert 激活触发器,也插入到b表

3).insert语句写入到bin log中

4).复制线程获得并执行insert语句

5).after insert 激活触发器,也插入到b表