标准格式
DECLARE
handler_type
HANDLER
FOR
condition_value[,...]
statement
handler_type:
CONTINUE
|
EXIT
|
UNDO
--这个暂时不支持
condition_value:
SQLSTATE
[VALUE]
sqlstate_value
|
condition_name
|
SQLWARNING
|
NOT
FOUND
|
SQLEXCEPTION
|
mysql_error_code
condition_value细节
1、常用MYSQL
ERROR
CODE
列表
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
更多错误列表见MySQL安装路径下
比如我的/usr/local/mysql/share/mysql/errmsg.txt
说明一下：SQLSTATE
[VALUE]
sqlstate_value这种格式是专门为ANSI
SQL
和
ODBC以及其他的标准.
并不是所有的MySQL
ERROR
CODE
都映射到SQLSTATE。
2、如果你不想插ERROR
CODE的话，就用速记条件来代替
SQLWARNING
代表所有以01开头的错误代码
NOT
FOUND
代表所有以02开头的错误代码，当然也可以代表一个游标到达数据集的末尾。
SQLEXCEPTION
代表除了SQLWARNING和NOT
FOUND
的所有错误代码
3、我们现在就用手册上的例子
CREATE
TABLE
t
(s1
int,primary
key
(s1))
mysql>
use
t_girl
Database
changed
mysql>
CREATE
TABLE
t
(s1
int,primary
key
(s1))
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
mysql>
mysql>
DELIMITER
||
mysql>
CREATE
PROCEDURE
handlerdemo
()
->
BEGIN
->
DECLARE
EXIT
HANDLER
FOR
SQLSTATE
'23000'
BEGIN
END
--
遇到重复键值就退出
->
SET
@x
=
1
->
INSERT
INTO
t
VALUES
(1)
->
SET
@x
=
2
->
INSERT
INTO
t
VALUES
(1)
->
SET
@x
=
3
->
END||
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
DELIMITER
mysql>
call
handlerdemo()
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
select
@x
+------+
|
@x
|
+------+
|
2
|
+------+
1
row
in
set
(0.00
sec)
mysql>
call
handlerdemo()
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
select
@x
+------+
|
@x
|
+------+
|
1
|
+------+
1
row
in
set
(0.00
sec)
mysql>
现在来看一下遇到错误继续的情况
mysql>
truncate
table
t
Query
OK,
0
rows
affected
(0.01
sec)
mysql>
DELIMITER
$$
mysql>
DROP
PROCEDURE
IF
EXISTS
`t_girl`.`handlerdemo`$$
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`handlerdemo`()
->
BEGIN
->
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'23000'
BEGIN
END
->
SET
@x
=
1
->
INSERT
INTO
t
VALUES
(1)
->
SET
@x
=
2
->
INSERT
INTO
t
VALUES
(1)
->
SET
@x
=
3
->
END$$
Query
OK,
0
rows
affected
(0.01
sec)
mysql>
DELIMITER
mysql>
call
handlerdemo()
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
select
@x
+------+
|
@x
|
+------+
|
3
|
+------+
1
row
in
set
(0.00
sec)
mysql>
call
handlerdemo()
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
select
@x
+------+
|
@x
|
+------+
|
3
|
+------+
1
row
in
set
(0.00
sec)
mysql>
可以看到，始终执行到最后。
当然，上面的SQLSTATE
'23000'可以替换为1062
我们来看一下警告。
mysql>
alter
table
t
add
s2
int
not
null
Query
OK,
0
rows
affected
(0.01
sec)
Records:
0
Duplicates:
0
Warnings:
0
此列没有默认值，插入的时候会出现警告或者1364错误提示。
mysql>
DELIMITER
$$
mysql>
DROP
PROCEDURE
IF
EXISTS
`t_girl`.`handlerdemo`$$
Query
OK,
0
rows
affected,
1
warning
(0.00
sec)
mysql>
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`handlerdemo`()
->
BEGIN
->
DECLARE
CONTINUE
HANDLER
FOR
1062
BEGIN
END
->
DECLARE
CONTINUE
HANDLER
FOR
SQLWARNING
->
BEGIN
->
update
t
set
s2
=
2
->
END
->
DECLARE
CONTINUE
HANDLER
FOR
1364
->
BEGIN
->
INSERT
INTO
t(s1,s2)
VALUES
(1,3)
->
END
->
SET
@x
=
1
->
INSERT
INTO
t(s1)
VALUES
(1)
->
SET
@x
=
2
->
INSERT
INTO
t(s1)
VALUES
(1)
->
SET
@x
=
3
->
END$$
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
DELIMITER
mysql>
call
handlerdemo()
Query
OK,
0
rows
affected
(0.00
sec)
mysql>
select
*
from
t
+----+----+
|
s1
|
s2
|
+----+----+
|
1
|
3
|
+----+----+
1
row
in
set
(0.00
sec)
遇到错误的时候插入的新记录。
mysql>
select
@x
+------+
|
@x
|
+------+
|
3
|
+------+
1
row
in
set
(0.00
sec)
1.new与old在数据库的触发器中经常会用到更新前的值和更新后的值，所以掌握new和old的语法很重要。
new：表示 *** 作执行后的数据行。
一User数据表如图User数据表所示，若执行以下更新 *** 作语句：updateUsersetscore=80whereuser_id=1
User数据表
则在此 *** 作中，old表示未执行update语句前user_id=1这行记录；而new表示执行update语句后user_id=1这行记录。
从上面的表述中可知，new与old均表示某一行记录，old所表示的是写 *** 作发生前的这一行旧数据，new则表示写 *** 作发生后的这一行新的数据。正因如此，可以把new与old看作面向对象编程里面的一个对象或实例，与面向对象的方式类似，可用new.字段名或old.字段名的方式进行存取值。
old.字段名：表示未执行 *** 作前的该行对应的某字段值。
new.字段名：表示执行 *** 作后的该行对应的某字段值。
在上面User表的update *** 作中：old.score=60，表示update *** 作前score字段的旧值是60。
new.score=80，表示update *** 作后score字段的新值是80。
如果要使用new语句进行赋值，只能在before类型的触发器中使用，不能在after类型的触发器中使用。
更新 *** 作前使用before先赋值，再插入数据库中。如以下语句是正确的：CREATETRIGGERupdatepriceBEFOREinsertONconsumeinfoFOREACHROWBEGINsetnew.金额=0END更新 *** 作后，不能在after中用new赋值，因为 *** 作已经结束，只能读取内容。如以下语句是错误的：CEATETRIGGERupdatepriceAFTERinsertONconsumeinfoFOREACHROWBEGINsetnew.金额=0ENDnew与old的区别：前者可在before触发器中赋值、取值，也可在after触发器中取值；
后者只能用于取值，因为赋值没有意义。
注意：INSERT语句，只有new合法；
DELETE语句，只有old才合法；
UPDATE语句，可以同时使用new和old。
2.before与afterbefore与after表示触发器触发的时间点是在写 *** 作开始之前，还是在写 *** 作完成后，正因为它们有时间点先后的问题，因此它们的功能与使用场合有非常大的差别。
（1)before（（1)先完成触发 *** 作，再执行业务数据的增删改。
（2）触发的语句先于监视的业务语句。
（3）有机会影响即将发生的 *** 作。
2）after（（1)先完成业务数据的增删改，再触发。
（2）触发的语句晚于监视的业务语句。
（3）无法影响前面的增删改动作。
3.异常处理MySQL现有版本中不支持自定义异常，当某处需要抛出异常时，可抛出一个系统异常（类似运行异常）。如故意往不存在的表中插入数据等方式来触发系统异常的抛出，当异常抛出时，本次正在执行的所有 *** 作会终止执行，并回滚所有数据到 *** 作发生前的状态。
在触发器中，需要抛出异常的场景有很多，以下两个方面较为普遍：（（1)新进来的数据不符合业务逻辑。
例如，①仓库最大商品库存数为10，订单要求一次性购买数量20。
②银行账户上只有50元，想要支付100元的账单。
（2）权限不足，不允许 *** 作。
例如，①普通用户通过非法途径 *** 作核心资源表。
②在非工作日修改业务数据。4.触发器案例用mytab.sql脚本创建表环境，然后用下面语句创建触发器。当往mytab表添加记录时，触发器中将抛出异常，导致所有 *** 作终止，并回滚所有 *** 作的数据。
DELIMITER//DROPTRIGGERIFEXISTSt_exception_triggerCREATETRIGGERt_exception_triggerAFTERINSERTONmytabFOREACHROWBEGIN--tab3表不存在
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）