#插入
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 //
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,分隔符问题