
/*------ ATD 376492 ------*/
-- Adicionando coluna CODFABRICANTEANTERIOR na tabela ALTERACAODESCRICAOITEM 
DECLARE v_count NUMBER(1) := 0; 
BEGIN 
SELECT 1 INTO v_count FROM USER_TAB_COLUMNS WHERE Upper(table_name) = 'ALTERACAODESCRICAOITEM' AND Upper(column_name) = 'CODFABRICANTEANTERIOR'; 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
EXECUTE IMMEDIATE 'ALTER TABLE ALTERACAODESCRICAOITEM ADD CODFABRICANTEANTERIOR VARCHAR(60) '; 
END;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Gravar_Alteracao_Descricao_Longa_Produto' AND type = 'TR')
   drop trigger Gravar_Alteracao_Descricao_Longa_Produto
go
CREATE TRIGGER Gravar_Alteracao_Descricao_Longa_Produto ON ComplementoProduto FOR UPDATE AS
BEGIN
	IF (UPDATE(DescricaoLonga) OR UPDATE(CODPROFABRICANTE))
	BEGIN
		DECLARE @cDescrAntiga VARCHAR(254), @cDescrNova VARCHAR(254), @cCodPro CHAR(5),
				@dDataIni DATETIME, @dDataFim DATETIME, @cCodProFabricanteAntigo VARCHAR(60),
				@cCodProFabricanteNovo VARCHAR(60)
		
		SELECT @cDescrAntiga = COALESCE(D.DescricaoLonga,''), @cDescrNova = COALESCE(I.DescricaoLonga,''), @cCodPro = COALESCE(I.CodPro,''), @cCodProFabricanteAntigo = COALESCE(D.CodProFabricante,''), @cCodProFabricanteNovo = COALESCE(I.CodProFabricante,'')
		FROM Inserted I
			JOIN Deleted D ON D.CodPro = I.CodPro
		
		IF @cCodPro <> '' AND ((@cDescrAntiga <> '' AND @cDescrNova <> '' AND @cDescrAntiga <> @cDescrNova))
		BEGIN
			SELECT TOP 1 @dDataIni = DATAFINAL
			FROM ALTERACAODESCRICAOITEM 
			WHERE CodPro = @cCodPro
				AND DESCRICAOLONGA = 1
			ORDER BY DATAFINAL DESC
			
			SET @dDataIni = COALESCE(@dDataIni,CAST('20100101' AS DATETIME))
			
			INSERT INTO ALTERACAODESCRICAOITEM (CODPRO, DESCRANTERIOR, DATAINICIAL, DATAFINAL, DESCRICAOLONGA, CODFABRICANTEANTERIOR) VALUES
												(@cCodPro, @cDescrAntiga, @dDataIni, GETDATE(), 1, @cCodProFabricanteAntigo)
		END
		IF @cCodPro <> '' AND ((@cCodProFabricanteAntigo <> '' AND @cCodProFabricanteNovo <> '' AND @cCodProFabricanteAntigo <> @cCodProFabricanteNovo))
		BEGIN
			SELECT TOP 1 @dDataIni = DATAFINAL
			FROM ALTERACAODESCRICAOITEM 
			WHERE CodPro = @cCodPro
				  AND DESCRICAOLONGA = 2
			ORDER BY DATAFINAL DESC
			
			SET @dDataIni = COALESCE(@dDataIni,CAST('20100101' AS DATETIME))
			
			INSERT INTO ALTERACAODESCRICAOITEM (CODPRO, DESCRANTERIOR, DATAINICIAL, DATAFINAL, DESCRICAOLONGA, CODFABRICANTEANTERIOR) VALUES
												(@cCodPro, @cDescrAntiga, @dDataIni, GETDATE(), 2, @cCodProFabricanteAntigo)
		END

	END
END
GO
