mysql5 触发器和存储过程

1. 存储过程例子如下：

名称 getcpidbycardid

in cdid int,out cpid int

BEGIN
#Routine body goes here...
SELECT
bcardtype.companyid into cpid
FROM
bcardbaseinfo
INNER JOIN bcardtype ON bcardtype.id = bcardbaseinfo.typeid
WHERE
bcardbaseinfo.id = cdid;
 
END

2. 触发器如下：

bcppay insert：

begin
set @tid:=NEW.cardid;
set @tcpshouldpay:=NEW.cpshouldpay;
set @tcpactualpay:=NEW.cpactualpay;
set @tcppaynumber:=NEW.cppaynumber;
IF  @tcpactualpay>0 && LENGTH(trim(@tcppaynumber))>0  THEN 
update `bcardstatus` set  `iscppayed`=1 where `cardid`=@tid;
call getcpidbycardid(@tid,@tcpid);
update `bcompanystatistic` set `shouldpaytotal`=(`shouldpaytotal`+ @tcpshouldpay),  `actualpaytotal`=( `actualpaytotal`+@tcpactualpay)
where companyid=@tcpid;
END IF;
end

bcppay update：

begin
set @tid:=OLD.cardid;
set @tcpshouldpay:=(NEW.cpshouldpay-OLD.cpshouldpay);
set @tcpactualpay:=(NEW.cpactualpay-OLD.cpactualpay);
call getcpidbycardid(@tid,@tcpid);
update `bcompanystatistic` set `shouldpaytotal`=(`shouldpaytotal`+ @tcpshouldpay),  `actualpaytotal`=( `actualpaytotal`+@tcpactualpay)
where companyid=@tcpid;
end

bcppay delete:

begin


set @tid:=OLD.cardid;
set @tcpshouldpay:=OLD.cpshouldpay;
set @tcpactualpay:=OLD.cpactualpay;
update `bcardstatus` set  `iscppayed`=0 where `cardid`=@tid;
call getcpidbycardid(@tid,@tcpid);
update `bcompanystatistic` set `shouldpaytotal`=(`shouldpaytotal`- @tcpshouldpay),  `actualpaytotal`=( `actualpaytotal`-@tcpactualpay)
where companyid=@tcpid;
end