50 lines
1.3 KiB
SQL
50 lines
1.3 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`supplier_beforeUpdate`
|
|
BEFORE UPDATE ON `supplier`
|
|
FOR EACH ROW
|
|
BEGIN
|
|
DECLARE vHasChange BOOL;
|
|
DECLARE vPayMethodChanged BOOL;
|
|
DECLARE vPayMethodHasVerified BOOL;
|
|
DECLARE vParams JSON;
|
|
DECLARE vOldPayMethodName VARCHAR(20);
|
|
DECLARE vNewPayMethodName VARCHAR(20);
|
|
|
|
SET NEW.editorFk = account.myUser_getId();
|
|
|
|
SELECT hasVerified INTO vPayMethodHasVerified
|
|
FROM payMethod
|
|
WHERE id = NEW.payMethodFk;
|
|
|
|
SET vPayMethodChanged = NOT(NEW.payMethodFk <=> OLD.payMethodFk);
|
|
|
|
IF vPayMethodChanged THEN
|
|
SELECT name INTO vOldPayMethodName
|
|
FROM payMethod
|
|
WHERE id = OLD.payMethodFk;
|
|
SELECT name INTO vNewPayMethodName
|
|
FROM payMethod
|
|
WHERE id = NEW.payMethodFk;
|
|
|
|
SET vParams = JSON_OBJECT(
|
|
'name', NEW.name,
|
|
'oldPayMethod', vOldPayMethodName,
|
|
'newPayMethod', vNewPayMethodName
|
|
);
|
|
SELECT util.notification_send('supplier-pay-method-update', vParams, NULL) INTO @id;
|
|
END IF;
|
|
|
|
SET vHasChange = NOT(NEW.payDemFk <=> OLD.payDemFk AND NEW.payDay <=> OLD.payDay) OR vPayMethodChanged;
|
|
|
|
IF vHasChange AND vPayMethodHasVerified THEN
|
|
SET NEW.isPayMethodChecked = FALSE;
|
|
END IF;
|
|
|
|
IF NOT (NEW.provinceFk <=> OLD.provinceFk)
|
|
OR (NEW.postCode <=> OLD.postCode) THEN
|
|
|
|
SET NEW.geoFk = supplier_getGeo(NEW.id);
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|