mysql一些簡單應用設定觸發禁止删除

1

<code>&lt;br data-filtered=</code><code>"filtered"</code><code>&gt;</code>

1.建立資料庫

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

2.建立資料表

部門表

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

Query OK, 0 rows affected (0.01 sec)

員工資訊表

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

員工薪資表

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

員工部門表

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

員工職位表

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

Query OK, 0 rows affected (0.00 sec)

3.插入資料

bu

MariaDB [utec]&gt; 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]&gt; select * from bu;

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

| bu_name   |

|     |

| caiwu     |

| it        |

| yewu      |

| yanfa     |

| gongcheng |

yg_info

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

MariaDB [utec]&gt; delimiter $

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

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

MariaDB [utec]&gt; delimiter ;

MariaDB [utec]&gt; call insert_info();       ##插入9999條資料

MariaDB [utec]&gt; create procedure insert_id() begin declare y int default 1; while y&lt;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$

MariaDB [utec]&gt; 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]&gt; create procedure utec_zong()

    -&gt; begin

    -&gt; update yg_info set sex='f' where id&lt;=5000;

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

    -&gt; update yg_money set money=1500 where id&lt;1000;

    -&gt; update yg_money set money=1400 where id&gt;=1000 and id&lt;3000;

    -&gt; update yg_money set money=1350 where id&gt;=3000 and id&lt;5000;

    -&gt; update yg_money set money=1200 where id&gt;=5000 and id&lt;7000;

    -&gt; update yg_money set money=1100 where id&gt;=7000 and id&lt;10000;

    -&gt; end$

MariaDB [utec]&gt; call utec_zong();

Query OK, 3000 rows affected (0.79 sec)

設定觸發  不允許插入ID&lt;1  和ID&gt;10000的

MariaDB [utec]&gt; create trigger bi_yginfo before insert on yg_info for each row begin  

if new.id&lt;1 then delete from yg_info where id&lt;1; 

elseif new.id&gt;10000 then delete from yg_info where id&gt;10000; 

end if; end$

MariaDB [utec]&gt; 

設定外鍵

MariaDB [utec]&gt; 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]&gt; 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]&gt; 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)                

測試下

MariaDB [utec]&gt; 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]&gt; insert into yg_info (id)values(10000);

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

MariaDB [utec]&gt; 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]&gt;                                  ##觸發限制

設定觸發不允許删除和更新 yg_info的ID列

MariaDB [utec]&gt; 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]&gt; create procedure rollbk()

    -&gt; rollback;

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

call rollbk; end if; end$

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

ERROR 1054 (42S22): Unknown column 'id' in 'where clause'   ##這個報錯是因為我的觸發禁止删除導緻   見下

MariaDB [utec]&gt; drop trigger delete_yginfo;

Query OK, 1 row affected (0.00 sec)

MariaDB [utec]&gt; select * from yg_info limit 2;

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

| id | name | sex  |

|  2 | yg2  | f    |

|  3 | yg3  | f    |

2 rows in set (0.00 sec)

本文轉自 am2012 51CTO部落格，原文連結:http://blog.51cto.com/goome/1966994