USE vn;

	DROP TRIGGER IF EXISTS invoiceIn_beforeUpdate;

	UPDATE invoiceIn 
		SET cplusRectificationTypeFk = NULL 
		WHERE cplusRectificationTypeFk = 1;

	DELETE IGNORE FROM cplusRectificationType WHERE id = 1;

	UPDATE cplusRectificationType 
		SET id = 1 
		WHERE id = 3;

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`invoiceIn_beforeUpdate`
	BEFORE UPDATE ON `invoiceIn`
	FOR EACH ROW
BEGIN
	DECLARE vWithholdingSageFk INT;

	IF NOT (NEW.supplierRef <=> OLD.supplierRef) AND NOT util.checkPrintableChars(NEW.supplierRef) THEN
		CALL util.throw('The invoiceIn reference contains invalid characters');
	END IF;

	SET NEW.editorFk = account.myUser_getId();

	IF (SELECT COUNT(*) FROM invoiceIn 
			WHERE supplierRef = NEW.supplierRef 
				AND supplierFk = NEW.supplierFk 
				AND YEAR(issued) = YEAR(NEW.issued)
				AND id <> NEW.id
	) THEN
		CALL util.throw('reference duplicated');
	END IF;

	IF 	NEW.supplierFk != OLD.supplierFk THEN
		CALL supplier_checkIsActive(NEW.supplierFk);
		SELECT withholdingSageFk INTO vWithholdingSageFk
				FROM supplier 
				WHERE id = NEW.supplierFk;
		SET NEW.withholdingSageFk = vWithholdingSageFk;
	END IF;

END$$
DELIMITER ;