是插入? 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 ;
如果是替换: update newtable set bb=if(aa=3,5,if(aa=2,10,bb))
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;
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: 0mysql> select * from newtable; +------+------+------+ | id | aa | bb | +------+------+------+ | 1 | 2 | 10 | | 2 | 3 | 5 | | 3 | 6 | 9 | +------+------+------+ 3 rows in set (0.00 sec)mysql>
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 ;
照着手册写,都能摸索出来。比拿现成的SQL要有收获多了。
把我自己写的发上来供大家参考 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 ;
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 ;
update newtable set bb=if(aa=3,5,if(aa=2,10,bb))
FOR EACH ROW
set new.bb=case new.aa when 3 then 5 when 2 then 10 else new.bb end;
-> 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: 0mysql> select * from newtable;
+------+------+------+
| id | aa | bb |
+------+------+------+
| 1 | 2 | 10 |
| 2 | 3 | 5 |
| 3 | 6 | 9 |
+------+------+------+
3 rows in set (0.00 sec)mysql>
是插入?
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 ;
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 ;