《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
建表：
create table LOGS ( LOG_ID number(*,0) PRIMARY KEY, LOG_TABLE char(10) NOT NULL, LOG_DML char(6) check (LOG_DML in('INSERT','DELETE','UPDATE')), LOG_KEY_ID char(10) NOT NULL, LOG_DATE date NOT NULL, LOG_USER char(15) NOT NULL ); 建序列
create sequence LOG_ID_sequence increment by 1 start with 1 NOMAXvalue nocycle nocache;
建触发器
create or replace trigger DML_LOG
after insert or delete or update on Score referencing new as new old as old for each row declare
v_ID LOGS.LOG_ID%type; v_TABLE LOGS.LOG_TABLE%type; v_DML LOGS.LOG_DML%type; v_KEY_ID LOGS.LOG_KEY_ID%type; v_DATE LOGS.LOG_DATE%type; v_USER LOGS.LOG_USER%type; begin
v_ID:=to_number(LOG_ID_sequence.nextval); v_TABLE:='Score'; select sysdate,user into v_DATE,v_USER from dual; if inserting then v_DML:='INSERT'; v_KEY_ID:=:new.SNO||'_'||:new.CNO; elsif deleting then v_DML:='DELETE'; v_KEY_ID:=:old.SNO||'_'||:old.CNO; elsif updating then v_DML:='UPDATE'; v_KEY_ID:=:old.SNO||'_'||:old.CNO; end if; insert into LOGS values(v_ID,v_TABLE,v_DML,v_KEY_ID,v_DATE,v_USER); end;
35/19
《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
测试
36/19