mysql 触发器实例

#插入
DELIMITER //
CREATE TRIGGER vtiger_receivable_ins BEFORE INSERT ON vtiger_receivable
FOR EACH ROW


BEGIN 
  declare conamt decimal(12,2);
  declare rcvttl decimal(12,2);  
  SELECT contractamount INTO conamt FROM vtiger_salescontract WHERE salescontractid=NEW.contractno;
  SELECT sum(receivableamount) INTO rcvttl FROM vtiger_receivable WHERE contractno=NEW.contractno;
  IF rcvttl> conamt THEN
     insert into rrr values(1);
  END IF;
  
  IF NEW.receivableamount>0 THEN
     UPDATE vtiger_salescontractcf
       SET cf_660= cf_660 + NEW.receivableamount,cf_661='backed' 
     WHERE salescontractid = NEW.contractno;
  END IF;
END;


#更新
DELIMITER //
CREATE TRIGGER vtiger_receivable_upd AFTER UPDATE ON vtiger_receivable
FOR EACH ROW


BEGIN 
 IF NEW.receivableamount > 0 THEN
    UPDATE vtiger_salescontractcf 
       SET cf_660 = cf_660- OLD.receivableamount+ NEW.receivableamount,cf_661='backed' 
WHERE salescontractid=OLD.contractno;
  END IF;
END;


#删除
DELIMITER //
CREATE TRIGGER vtiger_receivable_del AFTER DELETE ON vtiger_receivable
FOR EACH ROW


BEGIN 
  declare rcvttl decimal(12,2);
  declare revtotal decimal(12,2);


  SELECT sum(receivableamount) INTO rcvttl FROM vtiger_receivable WHERE contractno=OLD.contractno;


  SET revtotal=rcvttl- OLD.receivableamount; 
 IF revtotal > 0 THEN
    UPDATE vtiger_salescontractcf 
       SET cf_660 = revtotal,cf_661='backed' 
     WHERE salescontractid=OLD.contractno;
 ELSEIF revtotal=0 THEN 
    UPDATE vtiger_salescontractcf 
       SET cf_660 = revtotal,cf_661='notback' 
     WHERE salescontractid=OLD.contractno;
 END IF;
END;

如果没有DELIMITER //就会报错，因为mysql一遇到分号，它就要自动执行。即，在语句RETURN '';时，mysql解释器就要执行了。

这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

另外：mysql有很多限制

1.一次只能定义一个触发动作

2，设置值必须用set语句

3，分隔符问题