为了记录数据库shutdown和startup的时间,使用2个系统级触发器。[@more@]
create table LOGSERVICE
(
ID NUMBER(18) not null,
LOGINTIME DATE default sysdate,
LOGOUTTIME DATE,
MACHINENAME VARCHAR2(30)
);
create or replace trigger ta_database
after startup on database
begin
insert into LOGSERVICE(ID,LOGINTIME,LOGOUTTIME,MACHINENAME)
values(LOGSERVICE_sequence.nextval,sysdate,null,null);
end;
create or replace trigger tb_database
before shutdown on database
begin
update LOGSERVICE set LOGOUTTIME =sysdate where id in (select max(id) from LOGSERVICE);
end;
ORACLE系统触发器的类型与使用介绍(SQL没有系统触发器)
系统级触发器事件:
AFTER SERVERERROR 服务器错误触发
AFTER LOGON 登录后触发
BEFORE LOGOFF 退出登录前触发
AFTER STARTUP 启动数据库后触发
AFTER SUSPEND 数据库挂起后触发(9i新增)
BEFORE SHUTDOWN 数据库关闭前触发
猜想,即然有SUSPEND事件,应该就唤醒事件,不知如何写?创建系统触发器时需要指定作用范围:ON DATABASE或ON SCHEMA。
CREATE OR REPLACE TRIGGER TRIGGER_NAME
TIMING
DATABASE_EVENT1 or DATABSE_EVENT2 ON DATABASE|SCHEMA
TRIGGER BODY