Database triggers for different notifications

Database triggers for different notifications

--shutdown trigger
--startup trigger
--logon trigger
--logoff trigger
--server error trigger


Create a table to store the trigger information:
create table trigger_table (database_name varchar2(30), event_name varchar2(20), event_time date, triggered_by_user varchar2(30));

Set the date format a session level:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

--shutdown

create or replace trigger log_shutdown
before shutdown on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER', 'SHUTDOWN INITIATED', sysdate, user);
commit;
end;
/

--startup

create or replace trigger log_startup
after startup on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER','STARTUP INITIATED',sysdate,user);
commit;
end;
/

--logon

create table LOGON_table (login_date date, user_name varchar2(10), status varchar2(10));

create or replace trigger logon_trigger
after logon on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_in');
commit;
end logon_trigger;
/

--logout

create or replace trigger log_out
before logoff on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_out');
commit;
end logon_trigger;
/

--logon_failure

create or replace trigger logon_failures
after servererror on database
begin
if (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_table
(login_date, user_name, status)
values
(sysdate, sys_context('USERENV','AUTHENTICATED_IDENTITY'),'ORA-01017');
END IF;
COMMIT;
END logon_failures;
/