CREATE TRIGGER `test_before_ins_tr` BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
set @i= new.i; IF @i> 30 THEN
-- 在这里抛出异常和取消 insert 操作
end if; END;
请问如何实现 ????
FOR EACH ROW
BEGIN
set @i= new.i; IF @i> 30 THEN
-- 在这里抛出异常和取消 insert 操作
end if; END;
请问如何实现 ????
mysql> create table t_control(id int primary key);
Query OK, 0 rows affected (0.11 sec)mysql> insert into t_control values (1);
Query OK, 1 row affected (0.05 sec)mysql> create table t_bluerosehero(id int primary key,col int);
Query OK, 0 rows affected (0.11 sec)mysql> delimiter //
mysql> create trigger tr_t_bluerosehero_bi before insert on t_bluerosehero
-> for each row
-> begin
-> if new.col>30 then
-> insert into t_control values (1);
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;
mysql>
mysql> insert into t_bluerosehero values (1,20);
Query OK, 1 row affected (0.25 sec)mysql> insert into t_bluerosehero values (2,40);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
mysql> select * from t_bluerosehero;
+----+------+
| id | col |
+----+------+
| 1 | 20 |
+----+------+
1 row in set (0.00 sec)mysql>
mysql> create trigger tr_t_bluerosehero_bi before insert on t_bluerosehero
-> for each row
-> begin
-> declare i int;
-> if new.col>30 then
-> insert into xxxx values (1);
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.06 sec)mysql> delimiter ;
mysql> delete from t_bluerosehero;
Query OK, 3 rows affected (0.05 sec)mysql> insert into t_bluerosehero values (1,20);
Query OK, 1 row affected (0.06 sec)mysql> insert into t_bluerosehero values (2,40);
ERROR 1146 (42S02): Table 'csdn.xxxx' doesn't exist
mysql>
if new.col>30 then
select last_insert_into();
end if;
这样就可以抛出一个 sqlstate'42000' 的异常.这是我刚刚写一个存储过程时写错的一个地方,你试一下.ACMAIN_CHM大哥好热情,他是不是这个版的版主?
如果你真要实现,则可以像5楼那样,自己在IF里面部分特意写错一条SQL语句,不过,建议这条SQL语句的内容有点特别,以区分其它类似的错误以便抓取作特别处理。