-- ################################################
-- # Creator: Cyrille MODIANO
-- # Created: 2010/08/20
-- # Name: Start_stop_service_triggers.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # This script creates a trigger used to 
-- # run a scheduler job which call an external shell 
-- # script or executable.
-- # Useful to run a pre command before starting 
-- # and shutdown the DB
-- # Depending of your program it can be  mandatory
-- # to START THE LISTENER BEFORE THE DATABASE
-- ################################################

begin
DBMS_SCHEDULER.create_program (
   program_name => 'START_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');
   
dbms_scheduler.create_job  
  (job_name => 'START_SERVICE',  
   program_name=> 'START_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program START_SCRIPT');
end;
/

begin
DBMS_SCHEDULER.create_program (
   program_name => 'STOP_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');

dbms_scheduler.create_job  
  (job_name => 'STOP_SERVICE',  
   program_name=> 'STOP_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program STOP_SCRIPT');
end;
/

CREATE OR REPLACE TRIGGER START_SCRIPT AFTER STARTUP ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('START_SERVICE');
END;
/

CREATE OR REPLACE TRIGGER STOP_SCRIPT BEFORE SHUTDOWN ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('STOP_SERVICE');
END;
/