CREATE OR REPLACE TRIGGER CHECK4DBA_TGR
  AFTER GRANT OR REVOKE ON SCHEMA

DECLARE

   DUMMY          NUMBER(38);
   HLD_ACTION     VARCHAR2(6);
   HLD_NAME       VARCHAR2(15);
   HLD_TIME       DATE;
   HLD_USERID     VARCHAR2(15);
   HLD_GRANTEE    VARCHAR2(15);
   V_VERBAGE      VARCHAR2(4);
   V_MESSAGE      VARCHAR2(70);
   V_ACTION       VARCHAR2(6);

BEGIN

   SELECT COUNT(*) INTO DUMMY
     FROM SYS.AUD$
    WHERE ACTION# IN (114,115)
      AND OBJ$NAME# = 'DBA'
      AND TO_CHAR(TIMESTAMP#, 'YYYYMMDD HH24:MI') > to_char(sysdate - .1/24, 'yyyymmdd hh24:mi');
    
   IF DUMMY > 0 THEN

   SELECT ACTION#, OBJ$NAME#, TIMESTAMP#, USERID, AUTH$GRANTEE
     INTO HLD_ACTION, HLD_NAME, HLD_TIME, HLD_USERID, HLD_GRANTEE
     FROM SYS.AUD$
    WHERE ACTION# IN (114,115)
      AND OBJ$NAME# = 'DBA'
      AND TO_CHAR(TIMESTAMP#, 'YYYYMMDD HH24:MI') > to_char(sysdate - .1/24, 'yyyymmdd hh24:mi');

   IF    HLD_ACTION = 114           THEN
             V_ACTION  := 'GRANT';
             V_VERBAGE := 'TO';
   ELSE
             V_ACTION  := 'REVOKE';
             V_VERBAGE := 'FROM';
   END IF;

   V_MESSAGE := HLD_USERID||' '||V_ACTION||' DBA '||V_VERBAGE||' '||HLD_GRANTEE||' AT '||HLD_TIME;

   CALL send_email('OraAlert@frmaint.com','burtonl@frmaint.com','DBA Role Alert Message ','TEST TEST TEST');

   END IF;

END;

/