我们先来创建两张表:
CREATE TABLE `t_user` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`idcard` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
)
id | int(8) |
name | varchar(32) |
idcard | varchar(32) |
CREATE TABLE `t_idcard` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`number` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
)
id | int(8) |
number | varchar(32) |
注意:建表语句中,括起表名、列明的可不是单引号哦,而是这个`(就是数字1左边那个),千万要注意哦!
现在有一个需求,就是要在t_user表中插入一条用户信息,如果该用户的身份证不在t_idcard表中时,自动将其身份证号插入t_idcard表中。
触发器定义:
触发器是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。
触发器语法:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
trigger_name:触发器名称(后面有触发器的命名规范)。
trigger_time:触发器的执行时间。有2个值BEFORE或AFTER,表明触发器是在激活它的语句之前或之后触发。
trigger_event:表明激活触发器的语句和类型。可以是下列3种值:
INSERT:将新行插入表时(INSERT、LOAD DATA或REPLACE语句)
UPDATE:更改某一行时(UPDATE语句)
DELETE:从表中删除某一行时(DELETE或REPLACE语句)
tbl_name:触发器相关联的表名。该表必须为永久性表,不能将触发器与临时表以及视图相关联。
FOR EACH ROW:表明每行执行一次触发器,而不是对整个表执行一次(我的理解是,每行的操作都会触发,比如一次性操作10行,触发10次)。
trigger_stmt:触发器程序体。触发器程序可以使用begin和end作为开头和结尾,中间包含多条语句。
注意:相同的trigger_time和trigger_event,只能有一个触发器。
好了,现在根据上面的语法,我们可以来编写需要的触发器了。
CREATE TRIGGER `t_user_rai` AFTER INSERT ON `t_user` FOR EACH ROW insert into t_idcard(number) values(new.idcard)
现在触发器已经创建好了,现在可以试着在表t_user上插入一条用户信息,如果该用户的身份证信息不在t_idcard表中,则t_idcard表中会自动插入该身份证信息。
注意,你有可能遇到这样的异常:
这可能是因为mysql版本过旧的问题。没关系,我们修改一下/etc/my.cnf中的thread_stack=128k,将值改为256k就可以了。
接下来,是一个复杂点的例子,也是我实际当中遇到的问题。
我们先建立三张表:
CREATE TABLE `t_user` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
)
id | int(8) |
name | varchar(32) |
CREATE TABLE `t_product` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`create_user` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
)
id | int(8) |
name | varchar(32) |
create_user | varchar(32) |
CREATE TABLE `t_user_product` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`user_id` int(8) NOT NULL,
`product_id` int(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `t_user_product_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `t_user_product_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
id | int(8) |
user_id | int(8) |
product_id | int(8) |
我的需求是,表t_user_product维护的是user拥有product使用权的关系,而product的create user自动拥有该product的使用权。所以当向t_product表中插入一条信息时,自动在t_user_product表中插入一条create user与product关系的信息。下面是建立的触发器:
DELIMITER //
CREATE TRIGGER `t_product_rai` AFTER INSERT ON `t_product` FOR EACH ROW begin
declare userid int(8);
select id into userid from t_user where name = new.create_user;
insert into t_user_product(user_id, product_id) values(userid, new.id);
end//
declare 关键字用来定义变量,存储查询结果。
注意:我们要在触发器程序体的外面加上delimiter // ... //,否则会将;识别为语句的结束。
我们还可以使用if语句,判断查询出的userid是否为空。
/********************未完待续*********************/
触发器命名规范:
trigger_name = table_name_<R|S><A|B|I><I|U|D>
<R|S>:基于行级(row)还是语句级(statement)的触发器
<A|B|I>:after、before或者是instead of触发器
<I|U|D>:触发事件是insert、update还是delete。如果有多个触发事件则连着写
例如:
salary_rai:salary表的行级after触发器,触发事件是insert
employee_sbiud:employee表的语句级before触发器,触发事件是insert、update和delete