MySQL存储过程写触发器 mysql存储过程和触发器

转载

夜行者3号 2024-06-06 07:48:03

文章标签 MySQL存储过程写触发器触发器 mysql ci 文章分类 MySQL 数据库

测试表结构：

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=MyISAM
ROW_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_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';

7.触发器的存储形式

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

[root@sunss test]# ls
a.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=TRIGGERS
triggers='CREATE DEFINER=`mysqldba`@`%` TRIGGER `tau_a` AFTER UPDATE ON `a` \n    FOR EACH ROW BEGIN\n            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 BEGIN\n         insert into b values(NEW.id,NEW.cnt);            \n    END'
sql_modes=0 0
definers='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存储引擎