--数据库事件触发器
--1. 记录数据库登入和登出的时间
DROP SEQUENCE SEQ_DB_LOG_ID;
CREATE SEQUENCE SEQ_DB_LOG_ID
MINVALUE 0000000001
MAXVALUE 9999999999
START WITH 0000000001
INCREMENT BY 1;
DROP TABLE TB_DB_LOG;
CREATE TABLE TB_DB_LOG(
ID NUMBER(10),
USERNAME VARCHAR2(20),
DATA_BASE_NAME VARCHAR2(30),
LOG_ON_TIME DATE,
LOG_OFF_TIME DATE,
OPERATION VARCHAR2(100),
CONSTRAINT PK_DB_LOG PRIMARY KEY(ID)
);
CREATE OR REPLACE TRIGGER TGR_EMP_LALA
AFTER LOGON --此处不可以是'BEFORE LOFOON', 就是登入数据库前不可以触发触发器。
ON DATABASE
BEGIN
INSERT INTO TB_DB_LOG
VALUES(
SEQ_DB_LOG_ID.NEXTVAL,
'SYSTEM',
'ORCL',
SYSDATE,
NULL,
'LOGON'
);
END;
/
CREATE OR REPLACE TRIGGER TGR_EMP_LULU
BEFORE LOGOFF --此处不可以是'AFTER LOFOFF', 就是登出数据库后不可以触发触发器。
ON DATABASE
BEGIN
INSERT INTO TB_DB_LOG
VALUES(
SEQ_DB_LOG_ID.NEXTVAL,
'SYSTEM',
'ORCL',
NULL,
SYSDATE,
'LOGOFF'
);
END;
/
--2. 当记录表内数据达到1000条时,删除前500条。
DROP TRIGGER TGR_EMP_LILI;
CREATE OR REPLACE TRIGGER TGR_EMP_LILI
AFTER INSERT
ON TB_DB_LOG
DECLARE
ROWS_COUNT NUMBER(4);
BEGIN
SELECT COUNT(1) INTO ROWS_COUNT FROM TB_DB_LOG;
IF INSERTING AND ROWS_COUNT >999 THEN
DELETE FROM TB_DB_LOG WHERE ID < SEQ_DB_LOG_ID.CURRVAL - 500;
END IF;
END;
/
DELETE FROM TB_DB_LOG;
COMMIT;
SELECT * FROM TB_DB_LOG ORDER BY 1 DESC;
SELECT COUNT(1) FROM TB_DB_LOG;
------解决思路----------------------
TGR_EMP_LALA 和 TGR_EMP_LULU 失效了吗?
------解决思路----------------------
Your third trigger's sequence number not allowed there , Please validate it .