高分求触发器sql

[mkki]

新建一个newtable，包含3个字段id，aa,bb.
功能实现当字段aa值为3时，修改bb字段为5.
当字段aa值为2时，修改bb字段为10.
求高手指教！！！

[mkki]

急求，谢啦

[wwwwb]

是插入？
DELIMITER $$
CREATE TRIGGER ff1 BEFORE INSERT ON newtable
FOR EACH ROW
BEGIN
IF new.aa=3 THEN
SET new.bb=5;
END IF;
IF new.aa=2 THEN
SET new.bb=10;
END IF;
END$$
DELIMITER ;

[wwwwb]

如果是替换：
update newtable set bb=if(aa=3,5,if(aa=2,10,bb))

[ACMAIN_CHM]

CREATE TRIGGER tr_newtable_bi BEFORE INSERT ON newtable
FOR EACH ROW
set new.bb=case new.aa when 3 then 5 when 2 then 10 else new.bb end;

[ACMAIN_CHM]

mysql> CREATE TRIGGER tr_newtable_bi BEFORE INSERT ON newtable
    -> FOR EACH ROW
    -> set new.bb=case new.aa when 3 then 5 when 2 then 10 else new.bb end;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into newtable values (1,2,3),(2,3,4),(3,6,9);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from newtable;
+------+------+------+
| id   | aa   | bb   |
+------+------+------+
|    1 |    2 |   10 |
|    2 |    3 |    5 |
|    3 |    6 |    9 |
+------+------+------+
3 rows in set (0.00 sec)

mysql>

[chendong_j]

好多强淫抢分啊

[mkki]

谢谢各位，自己解决了

[wwwwb]

怎么解决的？

[zuoxingyu]

照着手册写，都能摸索出来。比拿现成的SQL要有收获多了。

[mkki]

把我自己写的发上来供大家参考
delimiter $$
drop trigger if exists s3.tr_insert_ip_internal$$
create trigger s3.tr_insert_ip_internal before insert
on s3.IP_INTERNAL for each row
begin
if (new.status=1 && new.type=2) || (new.status=1 && new.type=3) then
set new.baseline=2;
end if;
end$$
delimiter ;

delimiter $$
drop trigger if exists s3.tr_update_ip_internal$$
create trigger s3.tr_update_ip_internal before update
on s3.IP_INTERNAL for each row
begin
if (new.status=1 && new.type=2) || (new.status=1 && new.type=3) then
set new.baseline=2;
end if;
end$$
delimiter ;

[WWWWA]

OR
是插入？
DELIMITER $$
CREATE TRIGGER ff1 BEFORE INSERT ON newtable
FOR EACH ROW
BEGIN
SET new.bB=IF(new.aa=3,5,IF(new.aa=2,10,NEW.BB);
END$$
DELIMITER ;