CREATE OR REPLACE TRIGGER WEBANALYTIC.TRG_WEBDETAIL_ETL BEFORE
INSERT ON WEBANALYTIC.WEB_DETAIL FOR EACH ROW DECLARE
   sBrowserAggregate  VARCHAR2(255);
   sVersion  VARCHAR2(255);
BEGIN
   -- Section 1:  Do some cleanup on the GET field from Apache
   IF (UPPER(:NEW.GET) LIKE '%INDEX.HTM') THEN
      :NEW.GET := substr(:NEW.GET,1,(instr(UPPER(:NEW.GET),'INDEX.HTM')-1));
   ELSIF (UPPER(:NEW.GET) LIKE '%INDEX.ASP') THEN
      :NEW.GET := substr(:NEW.GET,1,(instr(UPPER(:NEW.GET),'INDEX.ASP')-1));
   ELSE
      -- just keep the original GET value from Apache...
      null;
   END IF;

   -- Section 2:  Here we perform some decomposition of the Apache USER_AGENT data
   :NEW.BROWSER := NVL(SUBSTR(:NEW.USER_AGENT,INSTRB(:NEW.USER_AGENT,';',1,1)+2, (INSTRB(:NEW.USER_AGENT,';',1,2)-INSTRB(:NEW.USER_AGENT,';',1,1))-2),'Bot');
   -- Section 3  
   :NEW.PLATFORM := NVL(SUBSTR(:NEW.USER_AGENT,INSTRB(:NEW.USER_AGENT,';',1,2)+2, (INSTRB(:NEW.USER_AGENT,';',1,3)-INSTRB(:NEW.USER_AGENT,';',1,2))-2),SUBSTR(:NEW.USER_AGENT,INSTRB(:NEW.USER_AGENT,';',1,2)+2, (INSTR(TRIM(:NEW.USER_AGENT),')')-INSTRB(:NEW.USER_AGENT,';',1,2)-2)));
   -- Section 4  
   IF (INSTR(UPPER(:NEW.USER_AGENT),'MSI',1,1)) <> 0 THEN
      sBrowserAggregate := 'Microsoft Internet Explorer';
   ELSIF (INSTR(UPPER(:NEW.USER_AGENT),'NETSCAPE',1,1)) <> 0 THEN
      sBrowserAggregate := 'Netscape';
   ELSIF (INSTR(UPPER(:NEW.USER_AGENT),'KONQUEROR',1,1)) <> 0 THEN
      sBrowserAggregate := 'Konqueror';
   ELSIF (INSTR(UPPER(:NEW.USER_AGENT),'BOT',1,1)) <> 0 THEN
      sBrowserAggregate := 'Bot';
   ELSIF (INSTR(UPPER(:NEW.USER_AGENT),'MOZILLA',10,1)) <> 0 THEN
      sBrowserAggregate := 'Mozilla';
   ELSE
      sBrowserAggregate := 'Other';
   END IF;
   :NEW.BROWSER_AGGREGATE := sBrowserAggregate;
   -- Section 5
   IF (UPPER(:NEW.USER_AGENT) LIKE '%MSIE%') THEN
      sVersion := SUBSTR(:NEW.USER_AGENT,INSTR(:NEW.USER_AGENT,'MSIE')+5,3);
   ELSIF (INSTR(:NEW.USER_AGENT,'Netscape',1,1) <> 0) THEN
      sVersion := SUBSTR(:NEW.USER_AGENT,INSTR(:NEW.USER_AGENT,'Netscape/')+9,3);
   ELSIF (INSTR(:NEW.USER_AGENT,'Netscape6',1,1) <> 0) THEN
      sVersion := SUBSTR(:NEW.USER_AGENT,INSTR(:NEW.USER_AGENT,'Netscape6/')+10,3);
   ELSE
      sVersion := 'Other';
   END IF;
   :NEW.VERSION := sVersion;

   -- Section 6:  Convert the request size field to KB
   :NEW.REQUEST_SIZE := (:NEW.REQUEST_SIZE / 1024);

END TRG_WEBDETAIL_ETL;
/