-- Views
--
-- Tracking and aggregating object views - triggers for denormalization
--
-- Copyright (C) 2003 Jeff Davis
-- @author Jeff Davis <davis@xarg.net>
-- Adapted for Oracle by Mario Aguado <maguado@innova.uned.es>
-- @author Mario Aguado <maguado@innova.uned.es>
-- @creation-date 28/06/2006
--
-- @cvs-id $Id: views-triggers.sql,v 1.2 2007/08/01 08:59:09 marioa Exp $
--
-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html


CREATE OR REPLACE TRIGGER VIEWS_VIEWS_INS_TR AFTER INSERT ON VIEWS_VIEWS
FOR EACH ROW
DECLARE 
	V_COUNT INTEGER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES WHERE OBJECT_ID = :NEW.OBJECT_ID;
    IF V_COUNT  < 1 THEN 
        INSERT  INTO VIEW_AGGREGATES (OBJECT_ID,VIEWS_COUNT,UNIQUE_VIEWS,LAST_VIEWED) 
        VALUES (:NEW.OBJECT_ID,1,1,SYSDATE);
    ELSE
        UPDATE VIEW_AGGREGATES 
           SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE 
         WHERE OBJECT_ID = :NEW.OBJECT_ID;
    END IF;
END;
/
CREATE OR REPLACE TRIGGER VIEWS_VIEWS_UPD_TR AFTER UPDATE ON VIEWS_VIEWS
FOR EACH ROW
BEGIN
    UPDATE VIEW_AGGREGATES 
       SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE
     WHERE OBJECT_ID = :NEW.OBJECT_ID;
END;
/

CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_INS_TR AFTER INSERT ON VIEWS_BY_TYPE
FOR EACH ROW
DECLARE 
	V_COUNT INTEGER;
BEGIN
	SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES_BY_TYPE WHERE OBJECT_ID = :NEW.OBJECT_ID AND VIEW_TYPE = :NEW.VIEW_TYPE;
  	IF V_COUNT < 1 THEN 
	        INSERT INTO VIEW_AGGREGATES_BY_TYPE (OBJECT_ID,VIEW_TYPE,VIEWS_COUNT,UNIQUE_VIEWS,LAST_VIEWED) 
        	VALUES (:NEW.OBJECT_ID,:NEW.VIEW_TYPE,1,1,SYSDATE);
   	ELSE
        	UPDATE VIEW_AGGREGATES_BY_TYPE
           	SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE 
         	WHERE OBJECT_ID = :NEW.OBJECT_ID
           	AND VIEW_TYPE = :NEW.VIEW_TYPE;
   	END IF;
END;
/

CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_UPD_TR AFTER UPDATE ON VIEWS_BY_TYPE
FOR EACH ROW
BEGIN
    UPDATE VIEW_AGGREGATES_BY_TYPE 
       SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE
     WHERE OBJECT_ID = :NEW.OBJECT_ID
       AND VIEW_TYPE = :NEW.VIEW_TYPE;
END;
/