Oracle PL/SQL/Trigger/Audit Log Table
Содержание
Log and audit data change
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> create table employee_audit (
2 id VARCHAR2(4 BYTE), 3 salary Number(8,2), 4 delete_date Date, 5 deleted_by VARCHAR2(15 BYTE) 6 ) 7 /
Table created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER employee_after_delete
2 AFTER DELETE 3 ON employee 4 FOR EACH ROW 5 6 DECLARE 7 v_username varchar2(10); 8 9 BEGIN 10 11 -- Find username of person performing the DELETE on the table 12 SELECT user INTO v_username 13 FROM dual; 14 15 -- Insert record into audit table 16 INSERT INTO employee_audit (id, salary, delete_date, deleted_by) 17 VALUES ( :old.id, :old.salary, sysdate, v_username ); 18 19 END; 20 /
Trigger created. SQL> SQL> delete from employee; 8 rows deleted. SQL> SQL> select * from employee_audit; ID SALARY DELETE_DA DELETED_BY
---------- --------- ---------------
01 1234.56 09-SEP-06 sqle 02 6661.78 09-SEP-06 sqle 03 6544.78 09-SEP-06 sqle 04 2344.78 09-SEP-06 sqle 05 2334.78 09-SEP-06 sqle 06 4322.78 09-SEP-06 sqle 07 7897.78 09-SEP-06 sqle 08 1232.78 09-SEP-06 sqle 8 rows selected. SQL> SQL> drop table employee_audit; Table dropped. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Logging All Operatins Using Autonumbering
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15)); Table created. SQL> SQL> CREATE TABLE myTableAudit(
2 id INT PRIMARY KEY NOT NULL, 3 Operation VARCHAR(10), 4 RecordedOn DATE DEFAULT SysDate, 5 OldName VARCHAR(50), 6 NewName VARCHAR(50), 7 OldPhone VARCHAR(15), 8 NewPhone VARCHAR(15) 9 );
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
2 AFTER INSERT OR DELETE OR UPDATE ON myTable
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
6 INSERT INTO myTableAudit (Operation, NewName, NewPhone)VALUES ("Insert ", :NEW.Name, :NEW.PhoneNo);
7 ELSIF DELETING THEN
8 INSERT INTO myTableAudit (Operation, OldName, OldPhone)VALUES ("Delete ", :OLD.Name, :OLD.PhoneNo);
9 ELSIF UPDATING THEN
10 INSERT INTO myTableAudit (Operation, OldName, OldPhone, NewName, NewPhone)VALUES ("Update ", :OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo);
11 END IF;
12 END;
13 /
Trigger created. SQL> SQL> drop table myTable; Table dropped. SQL> SQL> drop table myTableAudit; Table dropped. SQL>
</source>
Logging All Operations
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
2 PhoneNo VARCHAR(15));
Table created. SQL> SQL> CREATE TABLE myTableAudit
2 (id INT PRIMARY KEY NOT NULL, 3 Operation VARCHAR(10), 4 RecordedOn DATE DEFAULT SysDate, 5 OldName VARCHAR(50), 6 NewName VARCHAR(50), 7 OldPhone VARCHAR(15), 8 NewPhone VARCHAR(15));
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
2 AFTER INSERT OR DELETE OR UPDATE ON myTable 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 INSERT INTO myTableAudit (id, Operation, NewName, NewPhone) 7 VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo); 8 ELSIF DELETING THEN 9 INSERT INTO myTableAudit (id, Operation, OldName, OldPhone) 10 VALUES (1, "Delete ", :OLD.Name, :OLD.PhoneNo); 11 ELSIF UPDATING THEN 12 INSERT INTO myTableAudit (id, Operation, 13 OldName, OldPhone, NewName, NewPhone) 14 VALUES (1, "Update ", 15 :OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo); 16 END IF; 17 END; 18 /
Trigger created. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL> drop table myTableAudit; Table dropped.
</source>
Logging INSERT Operations
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
2 PhoneNo VARCHAR(15));
Table created. SQL> SQL> CREATE TABLE myTableAudit
2 (id INT PRIMARY KEY NOT NULL, 3 Operation VARCHAR(10), 4 RecordedOn DATE DEFAULT SysDate, 5 OldName VARCHAR(50), 6 NewName VARCHAR(50), 7 OldPhone VARCHAR(15), 8 NewPhone VARCHAR(15));
Table created. SQL> SQL> SQL> CREATE or replace TRIGGER myTableAuditTrigger
2 AFTER INSERT ON myTable 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO myTableAudit (id, Operation, NewName, NewPhone) 6 VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo); 7 END; 8 /
Trigger created. SQL> SQL> show errors No errors. SQL> SQL> drop table myTable; Table dropped. SQL> SQL> drop table myTableAudit; Table dropped. SQL>
</source>
Logging INSERT Operations With WHEN Conditions
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
2 PhoneNo VARCHAR(15));
Table created. SQL> SQL> CREATE TABLE myTableAudit
2 (id INT PRIMARY KEY NOT NULL, 3 Operation VARCHAR(10), 4 RecordedOn DATE DEFAULT SysDate, 5 OldName VARCHAR(50), 6 NewName VARCHAR(50), 7 OldPhone VARCHAR(15), 8 NewPhone VARCHAR(15));
Table created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTableAuditTrigger
2 AFTER INSERT ON myTable 3 FOR EACH ROW 4 WHEN (NEW.Name="Peter") 5 BEGIN 6 INSERT INTO myTableAudit (id, Operation, NewName, NewPhone) 7 VALUES (1, "Insert ", :NEW.Name, :NEW.PhoneNo); 8 END; 9 /
Trigger created. SQL> SQL> show errors No errors. SQL> SQL> drop table myTable; Table dropped. SQL> SQL> drop table myTableAudit; Table dropped.
</source>
Log insert, update, delete for a table
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> create table employees_log(
2 who varchar2(30), 3 when date );
Table created. SQL> SQL> create or replace trigger biud_employees_copy
2 before insert or update or delete 3 on employees 4 begin 5 insert into employees_log(who, when )values(user, sysdate ); 6 end; 7 /
Trigger created. SQL> SQL> update employees set salary = salary * 1.1; 8 rows updated. SQL> SQL> select * from employees_log; WHO WHEN
---------
sqle 13-JUN-08 SQL> SQL> delete from employees where department_id = 10; 0 rows deleted. SQL> SQL> select * from employees_log; WHO WHEN
---------
sqle 13-JUN-08 sqle 13-JUN-08 SQL> SQL> SQL> drop table employees; Table dropped. SQL> drop table employees_log;
SQL>
</source>
Log user name and system time in a trigger
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> create table employees_log(
2 who varchar2(30), 3 when date );
Table created. SQL> SQL> create trigger biud_employees_copy
2 before insert or update or delete 3 on employees 4 begin 5 insert into employees_log(who, when )values(user, sysdate ); 6 end; 7 /
Trigger created. SQL> SQL> update employees set salary = salary * 1.1; 8 rows updated. SQL> SQL> select * from employees_log; WHO WHEN
---------
sqle 13-JUN-08 SQL> SQL> delete from employees where department_id = 10; 0 rows deleted. SQL> SQL> select * from employees_log; WHO WHEN
---------
sqle 13-JUN-08 sqle 13-JUN-08 SQL> SQL> SQL> drop table employees; Table dropped. SQL> drop table employees_log;
SQL>
</source>
Trigger for auditing
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created.
SQL> SQL> CREATE TABLE DEPT$AUDIT (
2 DEPTNO NUMBER, 3 DNAME VARCHAR2(14 byte), 4 LOC VARCHAR2(13 byte), 5 CHANGE_TYPE VARCHAR2(1 byte), 6 CHANGED_BY VARCHAR2(30 byte), 7 CHANGED_TIME DATE 8 );
SQL> SQL> CREATE OR REPLACE TRIGGER auditDEPTAR AFTER
2 INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW 3 declare 4 my DEPT$audit%ROWTYPE; 5 begin 6 if inserting then my.change_type := "I"; 7 elsif updating then my.change_type :="U"; 8 else my.change_type := "D"; 9 end if; 10 11 my.changed_by := user; 12 my.changed_time := sysdate; 13 14 case my.change_type 15 when "I" then 16 my.DEPTNO := :new.DEPTNO; 17 my.DNAME := :new.DNAME; 18 my.LOC := :new.LOC; 19 else 20 my.DEPTNO := :old.DEPTNO; 21 my.DNAME := :old.DNAME; 22 my.LOC := :old.LOC; 23 end case; 24 25 insert into DEPT$audit values my; 26 end; 27 /
Trigger created. SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
