CREATE TRIGGER ALERT.FILESPACE_AI_FES
AFTER INSERT ON ALERT.FILESPACE
REFERENCING NEW_TABLE AS NEW_ROWS
FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC	

	CALL MYSCHEMA.CREATE_TEMP_TABLE(); -- CREATES THE TEMP TABLE

	FOR WIZ_STAT AS
		--SELECT CLAUSE GOES HERE		
	DO		
		CALL MYSCHEMA.STORE_INST_COUNTERS_TO_TEMP_TABLE(WIZ_STAT.ALERT_CONFIG_ID, WIZ_STAT.STATUS_ID, WIZ_STAT.ROWS_COUNT); --STORES THE DATA TO THE TEMP TABLE	
	END FOR;
	
	CALL MYSCHEMA.UPDATE_INST_COUNTERS();--COPIES THE DATA FROM TEMP TABLE TO DESTINATION ONE
END;

CREATE PROCEDURE WIZARD.UPDATE_INST_COUNTERS
SPECIFIC WIZARD.UPDATE_INST_COUNTERS
BEGIN
	
	DECLARE RAISED_STATUS_ID INTEGER;
	DECLARE SNOOZED_STATUS_ID INTEGER;
	DECLARE CLOSED_STATUS_ID INTEGER;
		
	DECLARE CURRENT_ID INTEGER;
	DECLARE CURRENT_RAIZED_INST_COUNT INTEGER DEFAULT  0 ;
	DECLARE CURRENT_SNOOZED_INST_COUNT INTEGER DEFAULT  0 ;
	
	DECLARE RECALCULATED_RAIZED_INST_COUNT INTEGER DEFAULT  0 ;
	DECLARE RECALCULATED_SNOOZED_INST_COUNT INTEGER DEFAULT  0 ;
	DECLARE RECALCULATED_WIZARD_STATUS INTEGER;
	
	DECLARE AT_END INTEGER;
	
	DECLARE C1 CURSOR FOR	
		SELECT
			ID,
			COALESCE(RAIZED_INST_COUNT,  0 ), 
			COALESCE(SNOOZED_INST_COUNT,  0 )
		FROM
			WIZARD.ALERTCONFIG
		WHERE ID IN (SELECT DISTINCT ALERT_CONFIG_ID FROM SESSION.WIZ_INST_STAT)
		FOR UPDATE OF RAIZED_INST_COUNT, SNOOZED_INST_COUNT, STATUS_ID WITH RS;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END =  1 ;

	CALL WIZARD.ALERTSTATUS_GET_STATUSES_ID(RAISED_STATUS_ID, SNOOZED_STATUS_ID, CLOSED_STATUS_ID);
		
	OPEN C1;
	
	SET AT_END =  0 ;
	FETCH C1 INTO CURRENT_ID, CURRENT_RAIZED_INST_COUNT, CURRENT_SNOOZED_INST_COUNT;
			
	WHILE AT_END =  0  DO	
		
		SET RECALCULATED_RAIZED_INST_COUNT = CURRENT_RAIZED_INST_COUNT + COALESCE((SELECT SUM(ROWS_COUNT) FROM SESSION.WIZ_INST_STAT WHERE ALERT_CONFIG_ID = CURRENT_ID AND STATUS_ID = RAISED_STATUS_ID),  0 );
		SET RECALCULATED_SNOOZED_INST_COUNT = CURRENT_SNOOZED_INST_COUNT + COALESCE((SELECT SUM(ROWS_COUNT) FROM SESSION.WIZ_INST_STAT WHERE ALERT_CONFIG_ID = CURRENT_ID AND STATUS_ID = SNOOZED_STATUS_ID),  0 );		
				
		IF(RECALCULATED_RAIZED_INST_COUNT >  0 ) THEN 
			SET RECALCULATED_WIZARD_STATUS = RAISED_STATUS_ID;
		ELSEIF RECALCULATED_SNOOZED_INST_COUNT >  0  THEN	
			SET RECALCULATED_WIZARD_STATUS = SNOOZED_STATUS_ID;
		ELSE 
			SET RECALCULATED_WIZARD_STATUS = CLOSED_STATUS_ID;
		END IF;
				
		UPDATE WIZARD.ALERTCONFIG 
		SET
			RAIZED_INST_COUNT = RECALCULATED_RAIZED_INST_COUNT,
			SNOOZED_INST_COUNT = RECALCULATED_SNOOZED_INST_COUNT,
			STATUS_ID = RECALCULATED_WIZARD_STATUS
		WHERE CURRENT OF C1;			
	
		FETCH C1 INTO CURRENT_ID, CURRENT_RAIZED_INST_COUNT, CURRENT_SNOOZED_INST_COUNT;
	END WHILE;					
		
END;

WHERE ID IN (SELECT DISTINCT ALERT_CONFIG_ID FROM SESSION.WIZ_INST_STAT)