Oracle PL/SQL/Trigger/Old New Value
Содержание
- 1 Create a trigger with "REFERENCING new AS"
- 2 If updating and new value equals old value
- 3 if ( updating or deleting ), if ( inserting or updating )
- 4 :old and :new Pseudo-records
- 5 Output new and old value in a before update trigger
- 6 Reference old and new value by column in a before update Trigger
- 7 These triggers demonstrate the use of the :new correlation identifier.
- 8 This trigger uses predicates to log changes to the myStudent table.
Create a trigger with "REFERENCING new AS"
<source lang="sql">
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER GenerateStudentID
2 BEFORE INSERT OR UPDATE ON lecturer 3 REFERENCING new AS new_student 4 FOR EACH ROW 5 BEGIN 6 SELECT 20001 7 INTO :new_student.ID 8 FROM dual; 9 END GenerateStudentID; 10 /
Trigger created. SQL> SQL> INSERT INTO lecturer (first_name, last_name)
2 VALUES ("L", "L");
1 row created. SQL> SQL> SQL> drop table lecturer; Table dropped.
</source>
If updating and new value equals old value
<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 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> SQL> alter table dept
2 add emp_count number 3 constraint must_be_between_3_8 4 check(emp_count between 3 and 8 OR emp_count = 0) 5 deferrable initially deferred;
SQL> SQL> update dept set emp_count = (select count(*) from emp where emp.deptno = dept.deptno )
2 /
SQL> SQL> alter table dept
2 modify emp_count NOT NULL;
SQL> SQL> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp 3 for each row 4 begin 5 if ( updating and :old.deptno = :new.deptno ) 6 then 7 return; 8 end if; 9 if ( inserting or updating ) 10 then 11 update dept set emp_count = emp_count+1 12 where deptno = :new.deptno; 13 end if; 14 if ( updating or deleting ) 15 then 16 update dept set emp_count = emp_count-1 17 where deptno = :old.deptno; 18 end if; 19 end; 20 /
SQL> SQL> drop table emp; SQL> drop table dept; SQL>
</source>
if ( updating or deleting ), if ( inserting or updating )
<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 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> SQL> alter table dept
2 add emp_count number 3 constraint must_be_between_3_8 4 check(emp_count between 3 and 8 OR emp_count = 0) 5 deferrable initially deferred;
SQL> SQL> update dept set emp_count = (select count(*) from emp where emp.deptno = dept.deptno )
2 /
SQL> SQL> alter table dept
2 modify emp_count NOT NULL;
SQL> SQL> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp 3 for each row 4 begin 5 if ( updating and :old.deptno = :new.deptno ) 6 then 7 return; 8 end if; 9 if ( inserting or updating ) 10 then 11 update dept set emp_count = emp_count+1 12 where deptno = :new.deptno; 13 end if; 14 if ( updating or deleting ) 15 then 16 update dept set emp_count = emp_count-1 17 where deptno = :old.deptno; 18 end if; 19 end; 20 /
SQL> SQL> drop table emp; SQL> drop table dept; SQL>
</source>
:old and :new Pseudo-records
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER GenerateStudentID
2 BEFORE INSERT OR UPDATE ON lecturer 3 FOR EACH ROW 4 BEGIN 5 SELECT 20001 6 INTO :new.ID 7 FROM dual; 8 END GenerateStudentID; 9 /
Trigger created. SQL> SQL> SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C004592) violated
SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
20001 Scott Lawson Computer Science 11
SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
Output new and old value in a before update trigger
<source lang="sql">
SQL> SQL> create table t ( x int, y int ); Table created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> SQL> create or replace trigger t_bufer
2 before update on t for each row 3 begin 4 dbms_output.put_line( "old.x = " || :old.x ||", old.y = " || :old.y ); 5 dbms_output.put_line( "new.x = " || :new.x ||", new.y = " || :new.y ); 6 end; 7 /
Trigger created. SQL> set serveroutput on SQL> update t set x = x+1; old.x = 1, old.y = 1 new.x = 2, new.y = 1 1 row updated. SQL> SQL> drop table t; Table dropped. SQL>
</source>
Reference old and new value by column in a before update Trigger
<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> -- 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> -- BEFORE UPDATE Trigger SQL> CREATE OR REPLACE TRIGGER employee_before_update
2 BEFORE UPDATE
3 ON employee
4 FOR EACH ROW
5
6 DECLARE
7 v_username varchar2(10);
8
9 BEGIN
10 -- Find username of person performing UPDATE on the table
11 SELECT user INTO v_username
12 FROM dual;
13
14 dbms_output.put_line("By: "||v_username);
15 dbms_output.put_line("Date: "||sysdate);
16 dbms_output.put_line(":new.id: "||:new.id);
17 dbms_output.put_line(":new.first_name: "||:new.first_name);
18 dbms_output.put_line(":new.last_name: "||:new.last_name);
19 dbms_output.put_line(":old.id: "||:old.id);
20 dbms_output.put_line(":old.first_name: "||:old.first_name);
21 dbms_output.put_line(":old.last_name: "||:old.last_name);
22
23 END;
24 /
Trigger created. SQL> SQL> update employee set first_name="newName"; By: sqle Date: 09-SEP-06
- new.id: 01
- new.first_name: newName
- new.last_name: Martin
- old.id: 01
- old.first_name: Jason
- old.last_name: Martin
By: sqle Date: 09-SEP-06
- new.id: 02
- new.first_name: newName
- new.last_name: Mathews
- old.id: 02
- old.first_name: Alison
- old.last_name: Mathews
By: sqle Date: 09-SEP-06
- new.id: 03
- new.first_name: newName
- new.last_name: Smith
- old.id: 03
- old.first_name: James
- old.last_name: Smith
By: sqle Date: 09-SEP-06
- new.id: 04
- new.first_name: newName
- new.last_name: Rice
- old.id: 04
- old.first_name: Celia
- old.last_name: Rice
By: sqle Date: 09-SEP-06
- new.id: 05
- new.first_name: newName
- new.last_name: Black
- old.id: 05
- old.first_name: Robert
- old.last_name: Black
By: sqle Date: 09-SEP-06
- new.id: 06
- new.first_name: newName
- new.last_name: Green
- old.id: 06
- old.first_name: Linda
- old.last_name: Green
By: sqle Date: 09-SEP-06
- new.id: 07
- new.first_name: newName
- new.last_name: Larry
- old.id: 07
- old.first_name: David
- old.last_name: Larry
By: sqle Date: 09-SEP-06
- new.id: 08
- new.first_name: newName
- new.last_name: Cat
- old.id: 08
- old.first_name: James
- old.last_name: Cat
8 rows updated. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL>
</source>
These triggers demonstrate the use of the :new correlation identifier.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER GenerateStudentID
2 BEFORE INSERT OR UPDATE ON lecturer 3 FOR EACH ROW 4 BEGIN 5 SELECT 20001 6 INTO :new.ID 7 FROM dual; 8 END GenerateStudentID; 9 /
Trigger created. SQL> SQL> INSERT INTO lecturer (first_name, last_name)
2 VALUES ("L", "L");
1 row created. SQL> SQL> SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME
-------------------- --------------------
MAJOR CURRENT_CREDITS
---------------
10001 Scott Lawson
Computer Science 11
10002 Mar Wells
History 4
10003 Jone Bliss
Computer Science 8
ID FIRST_NAME LAST_NAME
-------------------- --------------------
MAJOR CURRENT_CREDITS
---------------
10004 Man Kyte
Economics 8
10005 Pat Poll
History 4
10006 Tim Viper
History 4
ID FIRST_NAME LAST_NAME
-------------------- --------------------
MAJOR CURRENT_CREDITS
---------------
10007 Barbara Blues
Economics 7
10008 David Large
Music 4
10009 Chris Elegant
Nutrition 8
ID FIRST_NAME LAST_NAME
-------------------- --------------------
MAJOR CURRENT_CREDITS
---------------
10010 Rose Bond
Music 7
10011 Rita Johnson
Nutrition 8
10012 Sharon Clear
Computer Science 3
ID FIRST_NAME LAST_NAME
-------------------- --------------------
MAJOR CURRENT_CREDITS
---------------
20001 L L
13 rows selected. SQL> SQL> drop table lecturer; Table dropped.
</source>
This trigger uses predicates to log changes to the myStudent table.
<source lang="sql">
SQL> SQL> CREATE TABLE myLogTable (
2 change_type CHAR(1) NOT NULL, 3 changed_by VARCHAR2(8) NOT NULL, 4 timestamp DATE NOT NULL, 5 old_student_id NUMBER(5), 6 old_department CHAR(3), 7 old_course NUMBER(3), 8 old_grade CHAR(1), 9 new_student_id NUMBER(5), 10 new_department CHAR(3), 11 new_course NUMBER(3), 12 new_grade CHAR(1) 13 );
Table created. SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTrigger
2 BEFORE INSERT OR DELETE OR UPDATE ON myStudent 3 FOR EACH ROW 4 DECLARE 5 v_ChangeType CHAR(1); 6 BEGIN 7 IF INSERTING THEN 8 v_ChangeType := "I"; 9 ELSIF UPDATING THEN 10 v_ChangeType := "U"; 11 ELSE 12 v_ChangeType := "D"; 13 END IF; 14 15 INSERT INTO myLogTable 16 (change_type, changed_by, timestamp, 17 old_student_id, old_department, old_course, old_grade, 18 new_student_id, new_department, new_course, new_grade) 19 VALUES 20 (v_ChangeType, USER, SYSDATE, 21 :old.student_id, :old.department, :old.course, :old.grade, 22 :new.student_id, :new.department, :new.course, :new.grade); 23 END myTrigger; 24 /
Trigger created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created. SQL> SQL> select * from myLogTable; C CHANGED_ TIMESTAMP OLD_STUDENT_ID OLD OLD_COURSE O NEW_STUDENT_ID NEW NEW_COURSE N - -------- --------- -------------- --- ---------- - -------------- --- ---------- - I sqle 18-JUN-08 10000 CS 102 A I sqle 18-JUN-08 10002 CS 102 B I sqle 18-JUN-08 10003 CS 102 C I sqle 18-JUN-08 10000 HIS 101 A I sqle 18-JUN-08 10001 HIS 101 B I sqle 18-JUN-08 10002 HIS 101 B I sqle 18-JUN-08 10003 HIS 101 A I sqle 18-JUN-08 10004 HIS 101 C I sqle 18-JUN-08 10005 HIS 101 C I sqle 18-JUN-08 10006 HIS 101 E I sqle 18-JUN-08 10007 HIS 101 B C CHANGED_ TIMESTAMP OLD_STUDENT_ID OLD OLD_COURSE O NEW_STUDENT_ID NEW NEW_COURSE N - -------- --------- -------------- --- ---------- - -------------- --- ---------- - I sqle 18-JUN-08 10008 HIS 101 A I sqle 18-JUN-08 10009 HIS 101 D I sqle 18-JUN-08 10010 HIS 101 A I sqle 18-JUN-08 10008 NUT 307 A I sqle 18-JUN-08 10010 NUT 307 A I sqle 18-JUN-08 10009 MUS 410 B I sqle 18-JUN-08 10006 MUS 410 E I sqle 18-JUN-08 10011 MUS 410 B I sqle 18-JUN-08 10000 MUS 410 B 20 rows selected. SQL> SQL> select * from myStudent; STUDENT_ID DEP COURSE G
--- ---------- -
10000 CS 102 A
10002 CS 102 B
10003 CS 102 C
10000 HIS 101 A
10001 HIS 101 B
10002 HIS 101 B
10003 HIS 101 A
10004 HIS 101 C
10005 HIS 101 C
10006 HIS 101 E
10007 HIS 101 B
STUDENT_ID DEP COURSE G
--- ---------- -
10008 HIS 101 A
10009 HIS 101 D
10010 HIS 101 A
10008 NUT 307 A
10010 NUT 307 A
10009 MUS 410 B
10006 MUS 410 E
10011 MUS 410 B
10000 MUS 410 B
20 rows selected. SQL> SQL> drop table myLogTable; Table dropped. SQL> drop table myStudent; Table dropped. SQL> SQL>
</source>
