trigger.sql

-- Explicação incompleta

/*Para esta trigger foi criado dois tipo de variável que comportam tabelas
	- tp_tabela_af
	- tp_email_agentes

Esses novos tipos permitem o uso de váriaves que comportam colunas e linhas. Portanto posso usá-las 
em function e procedures  como parametros ou ter uma tabela como retorno de uma função.

Na trigger, uma função (dbo.fn_informacoes_af) gera uma tabela com informações do contrato (AF) 
tendo como entrar o número da AF.

A outra função (dbo.fn_listagem_email) gera uma lista de emails de um determinado número de AF ou contrato.

Para trabalhar com essas funções basta declarar variveis usando os tipos criados nesse script. ex:
	- DECLARE @tabela_agentes TP_TABELA_AGENTES
	- DECLARE @agentes_email TP_EMAIL_AGENTES

Para gerar as tabelas e armazenar nas variáveis, basta usar as funções. ex:
	 - INSERT INTO @tabela_af SELECT * FROM dbo.fn_informacoes_af(@af, @valor_anterior, @valor_atualizado)		
	 - INSERT INTO @agentes_email SELECT * FROM dbo.fn_listagem_email(@af)	

É possível usar comandos SQL com essas váriaves. ex:
	-- SELECT * FOM @tabela_agentes

*/

-- Basta fazer um update para testar 
update AF
set faf_codigo = 4
where af_Codigo = 2


-- Este tipo foi criado para trabalhar com informações da AF pedido pelo cliente.
CREATE TYPE tp_tabela_af AS TABLE
(
	fase_anterior INT NULL,
	fase_atual INT NULL,	
	cpf NVARCHAR(20) NOT NULL,
	nome_cliente NVARCHAR(140),
	valor_contrato MONEY,
	qtde_parcelas SMALLINT,
	valor_parcela MONEY,
	obs_recusa VARCHAR(1000),
	data_recusa SMALLDATETIME,	
	af_codigo INT,
	valor_refin MONEY,
	refin_ini SMALLINT,
	refin_fim SMALLINT,
	refim_mes SMALLDATETIME,
	cdi_codigo INT,
	tipo_contrato VARCHAR(20)
)

--Tipo de váriavel para receber tabelas com listagem de emails dos agentes do contrato.
CREATE TYPE tp_email_agentes as TABLE
(
	rep_codigo int, 
	rep_email nvarchar(200), 
	rep_nome NVARCHAR(200) , 
	Rep_cpf NVARCHAR(28)
)

--------------------------------------------------------------------------------------------------

/* Inicio da trigger, disparada após o update da linha. Ela comparará o registro anterior com o valor que foi atualizado
da tabela AF


*/
create trigger trg_verificacao_mudanca_fase
ON AF
AFTER UPDATE

AS 
BEGIN
	DECLARE @valor_atualizado VARCHAR(100)
	DECLARE @valor_anterior VARCHAR(100)
	DECLARE @string VARCHAR(500)
	DECLARE @tabela_af TP_TABELA_AF
	DECLARE @tabela_agentes TP_TABELA_AGENTES
	DECLARE @af VARCHAR(20)
	DECLARE @teste varchar(140)
	DECLARE @agentes_email TP_EMAIL_AGENTES

	declare @nome varchar(200)
	DECLARE @rep_nome varchar(100)
	DECLARE @cpf varchar(100)
	declare @email varchar(100)

	select @af = a.af_codigo, @valor_atualizado = a.faf_codigo,  @valor_anterior  = b.faf_codigo
	from inserted a, deleted b
	SET @string = 'Valor anterior: ' + @valor_anterior + ' , valor atualizado : ' + @valor_atualizado

	IF(@valor_anterior <> @valor_atualizado)
		BEGIN				
		
		INSERT INTO @tabela_af SELECT * FROM dbo.fn_informacoes_af(@af, @valor_anterior, @valor_atualizado)		
		INSERT INTO @agentes_email SELECT * FROM dbo.fn_listagem_email(@af)	
		EXECUTE	dbo.pr_envio_email_af @agentes_email, @tabela_af

		END
	ELSE
		BEGIN
		SET @string = 'Valor não modificado!'
		END

	PRINT(@string)		
END
-----------------------------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION dbo.fn_informacoes_af(@af INT, @fase_anterior VARCHAR(100), @fase_atual VARCHAR(100))
RETURNS TABLE
AS
RETURN
(		
	SELECT @fase_anterior fase_anterior, @fase_atual fase_atual, b.con_cpf, b.con_nome, a.af_valor_af, a.itb_qtde_parcelas, a.af_valor_parcelas, a.af_observacao_recusa,
		   a.af_data_recusa, a.af_codigo, a.af_refinan_valor, a.af_refinan_parc_ini, a.af_refinan_parc_fin,
		   af_refinan_mes_referencia, a.cdi_codigo, 
		   case dbo.fn_tipo_contrato(@af) 
		   when 1 then 'Novo' 
		   when 2 then 'Refin' 
           when 3 then 'Compra' 
		   when 4 then 'Misto'
           when 5 then 'Portabilidade'
		   end tipo_contrato
	FROM af AS a
	JOIN convenio b ON a.con_codigo = b.con_codigo
	where a.af_codigo = @af
);

----------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION dbo.fn_listagem_email(@af INT)
RETURNS TABLE
AS
RETURN
(
	  SELECT b.Rep_codigo, b.rep_email, b.Rep_nome, b.Rep_cpf
      FROM comissao_af a
      JOIN representante b ON a.rep_codigo = b.rep_codigo
      WHERE a.af_codigo = @af
);

drop procedure dbo.pr_envio_email_af
-----------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.pr_envio_email_af @lista_email tp_email_agentes READONLY, @informacoes_af tp_tabela_af READONLY
AS	
	DECLARE @email VARCHAR(100)		
	DECLARE email_cursor CURSOR
	
	FOR SELECT rep_email FROM @lista_email
	OPEN email_cursor
	FETCH NEXT FROM email_cursor INTO @email

	While @@FETCH_STATUS = 0
	BEGIN
					   
			PRINT(@email)			
			FETCH NEXT FROM email_cursor INTO @email	

	END;		
	close email_cursor
	deallocate email_cursor
	
GO
---------------------------------------------------------------------------------------------------------------------------