2013年11月18日 星期一

mysql-transaction-master-update update another table


delimiter $$
drop trigger if exists `updateTaxDocAfterPayrollUpdate`$$
CREATE TRIGGER updateTaxDocAfterPayrollUpdate
AFTER UPDATE ON payroll
FOR EACH ROW
BEGIN
    declare mNewYear int(4);
declare mNewMonth int(2);
declare mSumGrossPay int(10);
set mNewYear=year(new.dateend);
set mNewMonth=month(new.dateend);
select sum(grosspay) from payroll where employeeRecordid=new.employeeRecordid and taxyear=new.taxyear
into mSumGrosspay ;
update ir56b set amtofsalary=mSumGrosspay where employeeRecordid=new.employeeRecordid and taxyear=new.taxyear ;
END;
$$
delimiter ;



/********************/

delimiter $$
drop trigger if exists GRN_LINE_UpdateActionAfterUpdate;
CREATE TRIGGER GRN_LINE_UpdateActionAfterUpdate
After Update ON GRN_LINE
FOR EACH ROW
BEGIN
declare mqty double;
select GRNQTY FROM GRNQTY WHERE PO_RECORDID=NEW.RECORDID INTO mqty; 
UPDATE PO SET GRNQTY=mqty; 
END;
$$
delimiter ;

沒有留言:

張貼留言