mysql触发器禁止删除_mysql一些简单应用设置触发禁止删除

1.创建数据库

MariaDB [(none)]> create database utec default character set utf8 collate  utf8_general_ci;

2.创建数据表

部门表

MariaDB [utec]> create table bu(bu_name varchar(10));

Query OK, 0 rows affected (0.01 sec)

员工信息表

MariaDB [utec]> create table yg_info(id int,name varchar(10),sex enum('f','m'));

Query OK, 0 rows affected (0.01 sec)

员工薪资表

MariaDB [utec]> create table yg_money(id int,money int);

Query OK, 0 rows affected (0.01 sec)

员工部门表

MariaDB [utec]> create table yg_bu(id int,bu_name varchar(10));

Query OK, 0 rows affected (0.01 sec)

员工职位表

MariaDB [utec]> create table yg_identily(id int,jon varchar(10));

Query OK, 0 rows affected (0.00 sec)

3.插入数据

bu

MariaDB [utec]> insert into bu values('caiwu'),('it'),('yewu'),('yanfa'),('gongcheng');

Query OK, 5 rows affected (0.01 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [utec]> select * from bu;

+-----------+

| bu_name   |

+-----------+

|     |

| caiwu     |

| it        |

| yewu      |

| yanfa     |

| gongcheng |

+-----------+

yg_info

MariaDB [utec]> alter table yg_info change id id int not null auto_increment primary key;

MariaDB [utec]> delimiter $

MariaDB [utec]> create procedure insert_info() begin declare y int default 1; while y<10000 do insert into

yg_info(name) values(concat('yg',y)); set y=y+1; end while; end$

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> delimiter ;

MariaDB [utec]> call insert_info();       ##插入9999条数据

MariaDB [utec]> delimiter $

MariaDB [utec]> create procedure insert_id() begin declare y int default 1; while y<10000  do

insert into yg_money(id)values(y);

insert into yg_bu(id)values(y);

insert into yg_identily(id)values(y);

set y=y+1;end while; end$

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> delimiter ;

MariaDB [utec]> call insert_id();             ##更新其余几个表的ID

设定员工ID小于等于5000的 sex 为f  大于5000的为m

设定员工ID小于1000的工资 1500   ID在1001到3000的工资为1400

ID在3001到5000的为1350   ID在5001到7000的工资为 1200

ID为7001到9999的工资为1100

MariaDB [utec]> delimiter $

MariaDB [utec]> create procedure utec_zong()

-> begin

-> update yg_info set sex='f' where id<=5000;

-> update yg_info set sex='m' where id>5000;

-> update yg_money set money=1500 where id<1000;

-> update yg_money set money=1400 where id>=1000 and id<3000;

-> update yg_money set money=1350 where id>=3000 and id<5000;

-> update yg_money set money=1200 where id>=5000 and id<7000;

-> update yg_money set money=1100 where id>=7000 and id<10000;

-> end$

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> delimiter ;

MariaDB [utec]> call utec_zong();

Query OK, 3000 rows affected (0.79 sec)

设置触发  不允许插入ID<1  和ID>10000的

MariaDB [utec]> create trigger bi_yginfo before insert on yg_info for each row begin

if new.id<1 then delete from yg_info where id<1;

elseif new.id>10000 then delete from yg_info where id>10000;

end if; end$

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> delimiter ;

MariaDB [utec]>

设置外键

MariaDB [utec]> alter table yg_money add foreign key(id) references yg_info(id) on delete cascade on update cascade;

Query OK, 10000 rows affected (0.40 sec)

Records: 10000  Duplicates: 0  Warnings: 0

MariaDB [utec]> alter table yg_bu add foreign key(id) references yg_info(id) on delete cascade on update cascade;

Query OK, 9999 rows affected (0.13 sec)

Records: 9999  Duplicates: 0  Warnings: 0

MariaDB [utec]> alter table yg_identily add foreign key(id) references yg_info(id) on delete cascade on update cascade;

Query OK, 9999 rows affected (0.15 sec)

Records: 9999  Duplicates: 0  Warnings: 0

测试下

MariaDB [utec]> insert into yg_money(id)values(10000);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`utec`.`yg_money`, CONSTRAINT `yg_money_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yg_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

##外键约束

MariaDB [utec]> insert into yg_info (id)values(10000);

Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [utec]> insert into yg_info (id)values(10001);

ERROR 1442 (HY000): Can't update table 'yg_info' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

MariaDB [utec]>                                  ##触发约束

设置触发不允许删除和更新 yg_info的ID列

MariaDB [utec]> create trigger delete_yginfo before delete on yg_info for each row begin if old.id=id then rollback; end if; end$

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

##mysql不可以给触发器显式或隐式方式开始或结束事务的语句的语句，比如COMMIT,START TRANSACTION,ROLLBACK。

MariaDB [utec]> create procedure rollbk()

-> begin

-> rollback;

-> end$

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> create trigger delete_yginfo before delete on yg_info for each row begin if old.id=id then

call rollbk; end if; end$

Query OK, 0 rows affected (0.01 sec)

MariaDB [utec]> delimiter ;

MariaDB [utec]>

MariaDB [utec]> delete from yg_info where id=1;

ERROR 1054 (42S22): Unknown column 'id' in 'where clause'   ##这个报错是因为我的触发禁止删除导致   见下

MariaDB [utec]> drop trigger delete_yginfo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [utec]> delete from yg_info where id=1;

Query OK, 1 row affected (0.00 sec)

MariaDB [utec]> select * from yg_info limit 2;

+----+------+------+

| id | name | sex  |

+----+------+------+

|  2 | yg2  | f    |

|  3 | yg3  | f    |

+----+------+------+

2 rows in set (0.00 sec)

MariaDB [utec]>

本文转自 am2012 51CTO博客，原文链接:http://blog.51cto.com/goome/1966994