Oracle PL/SQL/Trigger/Old New Value — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:58, 26 мая 2010
Содержание
- 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"
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.
If updating and new value equals old value
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>
if ( updating or deleting ), if ( inserting or updating )
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>
:old and :new Pseudo-records
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>
Output new and old value in a before update trigger
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>
Reference old and new value by column in a before update Trigger
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>
These triggers demonstrate the use of the :new correlation identifier.
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.
This trigger uses predicates to log changes to the myStudent table.
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>