Notes

USE [NIKU]
GO
/****** Object: Trigger [niku].[T_IDEA_OODF_CA_IDEA] Script Date: 22/02/2022 14:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [niku].[T_IDEA_OODF_CA_IDEA] ON [niku].[ODF_CA_IDEA] AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON DECLARE @V_ID NUMERIC, @V_OBJECT_ID NUMERIC, @V_COUNT NUMERIC ,@SRCPTRVAL VARBINARY(16),@DSTPTRVAL VARBINARY(16), @V_LOOP_KEY NUMERIC, @V_LAST_KEY NUMERIC, @V_TRANSACTION_ID BIGINT, @ROW_COUNT NUMERIC, @AUDIT_ID NUMERIC
SELECT @ROW_COUNT = 0
SELECT @V_TRANSACTION_ID = TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION
IF EXISTS (SELECT 1 FROM DELETED) AND EXISTS (SELECT 1 FROM INSERTED) and ( update(gpa_attivita_da_fare) or
update(gpa_entro_il) or
update(gpa_in_carico) or
update(gpa_nota) or
update(gpa_data_chiuso) or
update(gpa_data_lista) ) BEGIN
SELECT @V_LAST_KEY = 0, @V_LOOP_KEY=0
/* BEGIN THE LOOP(INSTEAD OF A CURSOR) TO HANDLE MULTIPLE ROWS */
SELECT @V_LOOP_KEY = MIN(id) FROM inserted
WHILE @V_LOOP_KEY IS NOT NULL AND @V_LOOP_KEY <> @V_LAST_KEY
BEGIN
select @V_OBJECT_ID =id from inserted i where 1=1 and i.id = @V_LOOP_KEY
/* START BLOCK FOR COLUMN: gpa_attivita_da_fare*/
IF( UPDATE(gpa_attivita_da_fare) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_attivita_da_fare IS NULL AND D.gpa_attivita_da_fare IS NOT NULL)
OR (I.gpa_attivita_da_fare IS NOT NULL AND D.gpa_attivita_da_fare IS NULL) OR (I.gpa_attivita_da_fare != D.gpa_attivita_da_fare)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_attivita_da_fare', 'odf_ca_idea', 'gpa_attivita_da_fare',D.gpa_attivita_da_fare,
I.gpa_attivita_da_fare, D.gpa_attivita_da_fare, I.gpa_attivita_da_fare, 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_attivita_da_fare*/

/* START BLOCK FOR COLUMN: gpa_entro_il*/
IF( UPDATE(gpa_entro_il) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_entro_il IS NULL AND D.gpa_entro_il IS NOT NULL)
OR (I.gpa_entro_il IS NOT NULL AND D.gpa_entro_il IS NULL) OR (I.gpa_entro_il != D.gpa_entro_il)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_entro_il', 'odf_ca_idea', 'gpa_entro_il',niku.ODF_AUD_DATE_FCT(D.gpa_entro_il),
niku.ODF_AUD_DATE_FCT(I.gpa_entro_il), niku.ODF_AUD_DATE_FCT(D.gpa_entro_il), niku.ODF_AUD_DATE_FCT(I.gpa_entro_il), 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_entro_il*/

/* START BLOCK FOR COLUMN: gpa_in_carico*/
IF( UPDATE(gpa_in_carico) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_in_carico IS NULL AND D.gpa_in_carico IS NOT NULL)
OR (I.gpa_in_carico IS NOT NULL AND D.gpa_in_carico IS NULL) OR (I.gpa_in_carico != D.gpa_in_carico)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_in_carico', 'odf_ca_idea', 'gpa_in_carico',niku.ODF_AUD_STATIC_LOV_FCT('GPA_IN_CARICO',D.gpa_in_carico,NULL,NULL,'it'),
niku.ODF_AUD_STATIC_LOV_FCT('GPA_IN_CARICO',I.gpa_in_carico,NULL,NULL,'it'), D.gpa_in_carico, I.gpa_in_carico, 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_in_carico*/

/* START BLOCK FOR COLUMN: gpa_nota*/
IF( UPDATE(gpa_nota) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_nota IS NULL AND D.gpa_nota IS NOT NULL)
OR (I.gpa_nota IS NOT NULL AND D.gpa_nota IS NULL) OR (I.gpa_nota != D.gpa_nota)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_nota', 'odf_ca_idea', 'gpa_nota',D.gpa_nota,
I.gpa_nota, D.gpa_nota, I.gpa_nota, 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_nota*/

/* START BLOCK FOR COLUMN: gpa_data_chiuso*/
IF( UPDATE(gpa_data_chiuso) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_data_chiuso IS NULL AND D.gpa_data_chiuso IS NOT NULL)
OR (I.gpa_data_chiuso IS NOT NULL AND D.gpa_data_chiuso IS NULL) OR (I.gpa_data_chiuso != D.gpa_data_chiuso)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_data_chiuso', 'odf_ca_idea', 'gpa_data_chiuso',niku.ODF_AUD_5001929_FCT(D.gpa_data_chiuso),
niku.ODF_AUD_5001929_FCT(I.gpa_data_chiuso), D.gpa_data_chiuso, I.gpa_data_chiuso, 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_data_chiuso*/

/* START BLOCK FOR COLUMN: gpa_data_lista*/
IF( UPDATE(gpa_data_lista) AND EXISTS ( SELECT 1 FROM INSERTED I, DELETED D WHERE I.id= D.id and I.id = @V_LOOP_KEY and ((I.gpa_data_lista IS NULL AND D.gpa_data_lista IS NOT NULL)
OR (I.gpa_data_lista IS NOT NULL AND D.gpa_data_lista IS NULL) OR (I.gpa_data_lista != D.gpa_data_lista)) ))
BEGIN
EXEC CMN_ID_SP 'CMN_AUDITS', @V_ID OUTPUT
INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID, ATTRIBUTE_CODE, TABLE_NAME, COLUMN_NAME, VALUE_BEFORE ,
VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID)
SELECT @V_ID, 'idea', @V_OBJECT_ID, 'gpa_data_lista', 'odf_ca_idea', 'gpa_data_lista',D.gpa_data_lista,
I.gpa_data_lista, D.gpa_data_lista, I.gpa_data_lista, 'U', I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY, I.LAST_UPDATED_DATE, I.LAST_UPDATED_BY,
(SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION)
FROM INSERTED I, DELETED D WHERE I.id= D.id AND I.id = @V_LOOP_KEY
END
/* END BLOCK FOR COLUMN: gpa_data_lista*/
SELECT @V_LOOP_KEY = MIN(id) FROM inserted WHERE id > @V_LOOP_KEY
END /* END OF LOOP */
END
SET NOCOUNT OFF END