终于搞定了mysql的触发器

lakesun

我的触发器是“CREATE TRIGGER sms_chk
BEFORE INSERT
ON sms
FOR EACH ROW
if NEW.from_id='admin' then SET NEW.content='系统测试，请勿回复！'; end if;”
执行后老是提示错误，后来研究了N久，终于在官网看到了正确的范例。

原来创建触发器之前要先执行“DELIMITER |”将分隔符由“;”换成“|”，然后才能执行啊!修改后为：
DELIMITER |
CREATE TRIGGER sms_chk
BEFORE INSERT
ON sms
FOR EACH ROW
if NEW.from_id='admin' then SET NEW.content='系统测试，请勿回复！'; end if;
|
DELIMITER ;


希望能给朋友们提供一些帮助～

[ 本帖最后由 lakesun 于 2006-8-3 10:28 编辑 ]

manpo

delimiter |

CREATE TRIGGER ins_event_info_1 AFTER INSERT ON EVENT_INFO_1
  FOR EACH ROW BEGIN
      CASE NEW.SEVERITY
        WHEN 0 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,EMERGENCY) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,EMERGENCY=EMERGENCY+1;
        WHEN 1 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,ALERT) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,ALERT=ALERT+1;
        WHEN 2 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,CRITICAL) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,CRITICAL=CRITICAL+1;
        WHEN 3 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,ERROR) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,ERROR=ERROR+1;
        WHEN 4 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,WARNING) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,WARNING=WARNING+1;
        WHEN 5 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,NOTICE) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,NOTICE=NOTICE+1;
        ELSE
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,INFORMATIONAL) VALUES(date(NEW.TS),NEW.ZCBH,1,1) ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1,INFORMATIONAL=INFORMATIONAL+1;
      END CASE;

  END;
  |


delimiter ;



执行时报错：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN 1 THEN
          INSERT INTO EVENT_SUMMARY_PER_DAY (RQ,ZCBH,TOTAL,ALERT) VALUES(d' at line 1

yueliangdao0608

触发器会影响性能吗?