DROP TRIGGER IF EXISTS InsertAwbexport;

DELIMITER $$

CREATE TRIGGER 
	InsertAwbexport
AFTER INSERT
ON awbexport FOR EACH ROW
BEGIN
	INSERT INTO 
		modifiche_awbexport (
			exp_id, hash, tipo_hash, stato, creator_id, creation_date, modify_id, modify_date, tipo_aggiornamento
			, lpackage, wpackage, hpackage, gross_weight, net_weight, obj_code, status
		) VALUES (
			NEW.id, SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
			, 'SHA2 512', 0, 1, NOW(), 1, NOW(), 1
			, IFNULL(NEW.LPackage, 0), IFNULL(NEW.WPackage, 0), IFNULL(NEW.HPackage, 0)
			, IFNULL(NEW.GrossWeight, 0), IFNULL(NEW.NetWeight, 0), IFNULL(NEW.obj_code, 0)
			, 8
		)
  	ON DUPLICATE KEY UPDATE 
		hash = SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
		, tipo_hash = 'sha3'
		, stato = 0
		, modify_id = 1
		, modify_date = NOW()
		, tipo_aggiornamento = 1
		, lpackage = IFNULL(NEW.LPackage, 0)
		, wpackage = IFNULL(NEW.WPackage, 0)
		, hpackage = IFNULL(NEW.HPackage, 0)
		, gross_weight = IFNULL(NEW.GrossWeight, 0)
		, net_weight = IFNULL(NEW.NetWeight, 0)
		, status = 8;

END$$    

DELIMITER ;

DROP TRIGGER IF EXISTS UpdateAwbexport;

DELIMITER $$

CREATE TRIGGER 
	UpdateAwbexport
AFTER UPDATE
ON awbexport FOR EACH ROW
BEGIN
	INSERT INTO 
		modifiche_awbexport (
			exp_id, hash, tipo_hash, stato, creator_id, creation_date, modify_id, modify_date, tipo_aggiornamento
			, lpackage, wpackage, hpackage, gross_weight, net_weight, obj_code, status
		) VALUES (
			NEW.id, SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
			, 'SHA2 512', 0, 1, NOW(), 1, NOW(), 2
			, IFNULL(NEW.LPackage, 0), IFNULL(NEW.WPackage, 0), IFNULL(NEW.HPackage, 0)
			, IFNULL(NEW.GrossWeight, 0), IFNULL(NEW.NetWeight, 0), IFNULL(NEW.obj_code, 0)
			, 8
	)
  	ON DUPLICATE KEY UPDATE 
		hash = SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
		, tipo_hash = 'sha3'
		, stato = 0
		, modify_id = 1
		, modify_date = NOW()
		, tipo_aggiornamento = 2
		, lpackage = IFNULL(NEW.LPackage, 0)
		, wpackage = IFNULL(NEW.WPackage, 0)
		, hpackage = IFNULL(NEW.HPackage, 0)
		, gross_weight = IFNULL(NEW.GrossWeight, 0)
		, net_weight = IFNULL(NEW.NetWeight, 0)
		, status = 8;
END$$    

DELIMITER ;
