Oracle PL/SQL Tutorial/Trigger/Auidt Table
Содержание
Audit trigger
SQL>
SQL> CREATE TABLE game_player
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 marked VARCHAR2(1) DEFAULT "N",
6 pcmac VARCHAR2(1) DEFAULT "N",
7 score NUMBER,
8 CONSTRAINT game_player_pk
9 PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL> CREATE TABLE game_player_audit
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 old_marked VARCHAR2(1),
6 new_marked VARCHAR2(1),
7 old_pcmac VARCHAR2(1),
8 new_pcmac VARCHAR2(1),
9 old_score NUMBER,
10 new_score NUMBER,
11 change_date DATE,
12 operation VARCHAR2(6));
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER audit_game_players
2 AFTER INSERT OR UPDATE OR DELETE ON game_player
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
6 INSERT INTO game_player_audit(player_id,game_id,group_number,
7 new_marked,new_pcmac,new_score,
8 change_date,operation)
9 VALUES(:new.player_id,:new.game_id,:new.group_number,
10 :new.marked,:new.pcmac,:new.score,
11 SYSDATE,"INSERT");
12 ELSIF UPDATING THEN
13 INSERT INTO game_player_audit(player_id,game_id,group_number,
14 old_marked,new_marked,
15 old_pcmac,new_pcmac,
16 old_score,new_score,
17 change_date,operation)
18 VALUES(:new.player_id,:new.game_id,:new.group_number,
19 :old.marked,:new.marked,
20 :old.pcmac,:new.pcmac,
21 :old.score,:new.score,
22 SYSDATE,"UPDATE");
23 ELSIF DELETING THEN
24 INSERT INTO game_player_audit(player_id,game_id,group_number,
25 old_marked,old_pcmac,old_score,
26 change_date,operation)
27 VALUES(:old.player_id,:old.game_id,:old.group_number,
28 :old.marked,:old.pcmac,:old.score,
29 SYSDATE,"DELETE");
30 END IF;
31 END;
32 /
Trigger created.
SQL>
SQL>
SQL> drop table game_player;
Table dropped.
SQL>
SQL> drop table game_player_audit;
Table dropped.
SQL>
Logging All Operatins Using Autonumbering
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>
Logging All Operations
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.
Logging INSERT Operations
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>
Logging INSERT Operations With WHEN Conditions
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.
Trigger for auditing
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.
Use audit table in a trigger
SQL>
SQL>
SQL> create table myaudit(
2 id VARCHAR2(4 BYTE) NOT NULL,
3 old_value VARCHAR2(40 BYTE),
4 new_value VARCHAR2(40 BYTE)
5 );
Table created.
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> -- 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> CREATE OR REPLACE TRIGGER before_employee_salary_update
2 BEFORE UPDATE OF salary
3 ON employee
4 FOR EACH ROW WHEN (new.salary < old.salary * 0.75)
5 BEGIN
6 dbms_output.put_line("id = " || :old.id);
7 dbms_output.put_line("Old salary = " || :old.salary);
8 dbms_output.put_line("New salary = " || :new.salary);
9 dbms_output.put_line("The salary reduction is more than 25%");
10
11 INSERT INTO Myaudit (
12 id, old_value, new_value
13 ) VALUES (
14 :old.id, :old.salary, :new.salary
15 );
16 END before_employee_salary_update;
17 /
Trigger created.
SQL>
SQL> update employee set salary = 0;
8 rows updated.
SQL>
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 0 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 0 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 0 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 0 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 0 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 0 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 0 New York Manager
08 James Cat 17-SEP-96 15-APR-02 0 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from myaudit;
ID OLD_VALUE NEW_VALUE
---- ---------------------------------------- ----------------------------------------
01 1234.56 0
02 6661.78 0
03 6544.78 0
04 2344.78 0
05 2334.78 0
06 4322.78 0
07 7897.78 0
08 1232.78 0
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL> drop table myaudit;
Table dropped.
SQL>
SQL>
SQL>