-- System triggers to log all startups and shutdowns (except abort) 
-- to an internal table
-- Copyright (c) 2004, Caleb.com

-- GRANT administer database trigger TO CALEB;

-- Trigger to log startup
CREATE OR REPLACE TRIGGER cs_startup
AFTER STARTUP ON DATABASE
DECLARE
   v_time   INTEGER;
BEGIN
   -- No need to commit, in fact you can't commit or rollback,
   -- logon/logoff triggers execute in their own transaction
   INSERT INTO cs_log
      VALUES ( sysdate, ora_login_user, 'STARTUP');
   -- Raising an exception here will cause the trigger txn to rollback,
   -- but will NOT prevent the database from starting.
   raise_application_error(-20004,'STARTUP denied!');
END;
/

-- Trigger to log shutdown
CREATE OR REPLACE TRIGGER cs_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
   INSERT INTO cs_log
      VALUES (sysdate, ora_login_user, 'SHUTDOWN');
   -- Likewise, an exception here will rollback the txn but DB will close anyway.
   raise_application_error(-20005,'SHUTDOWN denied!');
END;
/