Oracle PL/SQL/Trigger/Create Trigger — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Autonumbering Trigger
- 2 Cascade inserts into myStudent into session and lecturer.
- 3 create or replace trigger
- 4 Create tigger on wrapper table
- 5 Creating a Trigger with cursor inside
- 6 Empty trigger(before insert or update or delete)
- 7 Oracle"s syntax for creating a trigger based on two tables
- 8 Show errors for a trigger
- 9 Submit job from a trigger
- 10 This trigger sends messages over a pipe to record inserts into myStudent.
- 11 Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
- 12 Trigger on each row
- 13 Trigger Which Modifies a Mutating Table
- 14 Use RAISE_APPLICATION_ERROR in a trigger
- 15 Use Sequence in a trigger
- 16 Use sysdate and user function in a trigger
Autonumbering Trigger
SQL>
SQL>
SQL> CREATE SEQUENCE idSeq;
Sequence created.
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> CREATE OR REPLACE TRIGGER idAutonumberTrigger
2 BEFORE INSERT ON myTableAudit
3 FOR EACH ROW
4 BEGIN
5 SELECT idSeq.NEXTVAL
6 INTO :NEW.id FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> drop SEQUENCE idSeq;
Sequence dropped.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.
SQL>
Cascade inserts into myStudent into session and lecturer.
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> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
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> CREATE OR REPLACE TRIGGER myTrigger
2 BEFORE INSERT ON myStudent
3 FOR EACH ROW
4 DECLARE
5 v_Credits session.num_credits%TYPE;
6 BEGIN
7 SELECT num_credits
8 INTO v_Credits
9 FROM session
10 WHERE department = :new.department
11 AND course = :new.course;
12
13 UPDATE lecturer
14 SET current_credits = current_credits + v_Credits
15 WHERE ID = :new.student_id;
16
17 UPDATE session
18 SET current_lecturer = current_lecturer + 1
19 WHERE department = :new.department
20 AND course = :new.course;
21 END myTrigger;
22 /
Trigger created.
SQL>
SQL>
SQL>
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> drop table session;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
create or replace trigger
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", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL>
SQL> create or replace trigger biud_employees
2 before insert or update or delete
3 on employees
4 begin
5 null;
6 end;
7 /
Trigger created.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>
Create tigger on wrapper table
SQL>
SQL>
SQL> set echo one
SP2-0265: echo must be set ON or OFF
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create table upper_ename( x$ename, x$rid,primary key (x$ename,x$rid))
2 organization index
3 as
4 select upper(ename), rowid from emp;
SQL>
SQL> create or replace trigger upper_ename
2 after insert or update or delete on emp
3 for each row
4 begin
5 if (updating and (:old.ename||"x" <> :new.ename||"x")) then
6 delete from upper_ename
7 where x$ename = upper(:old.ename)
8 and x$rid = :old.rowid;
9
10 insert into upper_ename
11 (x$ename,x$rid) values
12 ( upper(:new.ename), :new.rowid );
13 elsif (inserting) then
14 insert into upper_ename
15 (x$ename,x$rid) values
16 ( upper(:new.ename), :new.rowid );
17 elsif (deleting) then
18 delete from upper_ename where x$ename = upper(:old.ename) and x$rid = :old.rowid;
19 end if;
20 end;
21 /
Trigger created.
SQL>
SQL> update emp set ename = initcap(ename);
14 rows updated.
SQL>
SQL> select * from upper_ename;
X$ENAME X$RID
---------- ------------------
ADAMS AAAD3HAABAAAIF6AAK
ADAMS AAAEJwAABAAAIF6AAK
ALLEN AAAD3HAABAAAIF6AAB
ALLEN AAAEJwAABAAAIF6AAB
X$ENAME X$RID
---------- ------------------
BLAKE AAAD3HAABAAAIF6AAF
BLAKE AAAEJwAABAAAIF6AAF
CLARK AAAD3HAABAAAIF6AAG
CLARK AAAEJwAABAAAIF6AAG
FORD AAAD3HAABAAAIF6AAM
FORD AAAEJwAABAAAIF6AAM
JAMES AAAD3HAABAAAIF6AAL
JAMES AAAEJwAABAAAIF6AAL
JONES AAAD3HAABAAAIF6AAD
JONES AAAEJwAABAAAIF6AAD
KING AAAD3HAABAAAIF6AAI
KING AAAEJwAABAAAIF6AAI
MARTIN AAAD3HAABAAAIF6AAE
MARTIN AAAEJwAABAAAIF6AAE
MILLER AAAD3HAABAAAIF6AAN
MILLER AAAEJwAABAAAIF6AAN
SCOTT AAAD3HAABAAAIF6AAH
SCOTT AAAEJwAABAAAIF6AAH
SMITH AAAD3HAABAAAIF6AAA
SMITH AAAEJwAABAAAIF6AAA
TURNER AAAD3HAABAAAIF6AAJ
TURNER AAAEJwAABAAAIF6AAJ
WARD AAAD3HAABAAAIF6AAC
WARD AAAEJwAABAAAIF6AAC
28 rows selected.
SQL> drop table emp;
Table dropped.
SQL>
SQL> --
Creating a Trigger with cursor inside
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> CREATE TABLE major_stats (
2 major VARCHAR2(30),
3 total_credits NUMBER,
4 total_lecturer NUMBER);
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT OR DELETE OR UPDATE ON lecturer
3 DECLARE
4 CURSOR c_Statistics IS
5 SELECT major, COUNT(*) total_lecturer,
6 SUM(current_credits) total_credits
7 FROM lecturer
8 GROUP BY major;
9 BEGIN
10 FOR v_StatsRecord in c_Statistics LOOP
11 UPDATE major_stats
12 SET total_credits = v_StatsRecord.total_credits,
13 total_lecturer = v_StatsRecord.total_lecturer
14 WHERE major = v_StatsRecord.major;
15 IF SQL%NOTFOUND THEN
16 INSERT INTO major_stats (major, total_credits, total_lecturer)
17 VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
18 v_StatsRecord.total_lecturer);
19 END IF;
20 END LOOP;
21 END myTrigger;
22 /
Trigger created.
SQL>
SQL> drop table major_stats;
Table dropped.
SQL> drop table lecturer;
Table dropped.
SQL>
Empty trigger(before insert or update or delete)
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", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> create or replace trigger d_employees
2 before insert or update or delete
3 on employees
4 begin
5 null;
6 end;
7 /
Trigger created.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
Oracle"s syntax for creating a trigger based on two tables
SQL>
SQL>
SQL>
SQL> CREATE TABLE myTable1 (a INTEGER, b CHAR(10));
Table created.
SQL>
SQL> CREATE TABLE myTable2 (c CHAR(10), d INTEGER);
Table created.
SQL>
SQL>
SQL> CREATE TRIGGER trig1
2 AFTER INSERT ON myTable1
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 WHEN (newRow.a <= 10)
6 BEGIN
7 INSERT INTO myTable2 VALUES(:newRow.b, :newRow.a);
8 END trig1;
9
Trigger created.
SQL>
SQL> insert into myTable1 values(1,"a");
1 row created.
SQL> insert into myTable1 values(2,"b");
1 row created.
SQL>
SQL> select * from myTable1;
A B
---------- ----------
1 a
2 b
SQL> select * from myTable2;
C D
---------- ----------
a 1
b 2
SQL>
SQL> drop table myTable1;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
Show errors for a trigger
SQL>
SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY,
3 gift_type_id INTEGER ,
4 name VARCHAR2(30) NOT NULL,
5 description VARCHAR2(50),
6 price NUMBER(5, 2)
7 );
Table created.
SQL>
SQL>
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, "Flower", "Birthday", 19.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, "Computer", "New Year", 30.00);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, "iPod", "Birthday", 25.99);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, "iPhone", "New Year", 13.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, "Book", "Birthday", 49.99);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_gift_price_update
2 BEFORE UPDATE OF price
3 ON gifts
4 FOR EACH ROW WHEN (new.price < old.price * 0.75)
5 BEGIN
6 dbms_output.put_line("gift_id = " || :old.gift_id);
7 dbms_output.put_line("Old price = " || :old.price);
8 dbms_output.put_line("New price = " || :new.price);
9 dbms_output.put_line("The price reduction is more than 25%");
10
11 INSERT INTO gift_price_audit (gift_id, old_price, new_price)VALUES(:old.gift_id, :old.price, :new.price);
12 END before_gift_price_update;
13 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER BEFORE_GIFT_PRICE_UPDATE:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------------------------------------------------------------
7/3
PL/SQL: SQL Statement ignored
7/15
PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL> drop table gifts;
Table dropped.
Submit job from a trigger
SQL>
SQL>
SQL> create table myTable
2 (id number,
3 recipient varchar2(80),
4 gid number);
Table created.
SQL>
SQL> create or replace trigger worknotbr before insert
2 on myTable for each row
3 declare
4 jobno number;
5 begin
6 dbms_job.submit(job => jobno, what => "email( job );");
7 :new.gid:= jobno;
8 end;
9 /
Trigger created.
SQL> create or replace procedure email (job in number) is
2 lv_recipient myTable.recipient%type;
3 begin
4 select recipient into lv_recipient from myTable where gid = job;
5 end;
6 /
Procedure created.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
This trigger sends messages over a pipe to record inserts into myStudent.
CREATE TABLE myStudent (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1)
);
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10000, "CS", 102, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10002, "CS", 102, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10003, "CS", 102, "C");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10000, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10001, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10002, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10003, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10004, "HIS", 101, "C");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10005, "HIS", 101, "C");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10006, "HIS", 101, "E");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10007, "HIS", 101, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10008, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10009, "HIS", 101, "D");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10010, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10008, "NUT", 307, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10010, "NUT", 307, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10009, "MUS", 410, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10006, "MUS", 410, "E");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10011, "MUS", 410, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10000, "MUS", 410, "B");
CREATE OR REPLACE TRIGGER LogRSInserts
BEFORE INSERT ON myStudent
FOR EACH ROW
DECLARE
v_Status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE("I");
DBMS_PIPE.PACK_MESSAGE(user);
DBMS_PIPE.PACK_MESSAGE(sysdate);
DBMS_PIPE.PACK_MESSAGE(:new.student_ID);
DBMS_PIPE.PACK_MESSAGE(:new.department);
DBMS_PIPE.PACK_MESSAGE(:new.course);
DBMS_PIPE.PACK_MESSAGE(:new.grade);
v_Status := DBMS_PIPE.SEND_MESSAGE("RSInserts");
IF v_Status != 0 THEN
RAISE_APPLICATION_ERROR(-20010, "LogRSInserts trigger " ||
"couldn""t send the message, status = " || v_Status);
END IF;
END LogRSInserts;
/
show errors
drop table myStudent;
Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
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> 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> CREATE OR REPLACE TRIGGER myTrigger
2 BEFORE INSERT OR DELETE OR UPDATE ON myStudent
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 v_ChangeType CHAR(1);
7 BEGIN
8 IF INSERTING THEN
9 v_ChangeType := "I";
10 ELSIF UPDATING THEN
11 v_ChangeType := "U";
12 ELSE
13 v_ChangeType := "D";
14 END IF;
15
16 INSERT INTO myLogTable
17 (change_type, changed_by, timestamp,
18 old_student_id, old_department, old_course, old_grade,
19 new_student_id, new_department, new_course, new_grade)
20 VALUES
21 (v_ChangeType, USER, SYSDATE,
22 :old.student_id, :old.department, :old.course, :old.grade,
23 :new.student_id, :new.department, :new.course, :new.grade);
24 COMMIT;
25 END myTrigger;
26 /
Trigger created.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL> drop table myLogTable;
Table dropped.
SQL>
Trigger on each row
create table tt
( x int,
constraint t_pk primary key(x)
)
/
create or replace trigger t_trigger
AFTER update on TT for each row
begin
dbms_output.put_line( "Updated x=" || :old.x || " to x=" || :new.x );
end;
/
insert into tt values ( 1 );
insert into tt values ( 2 );
/
set serveroutput on
begin
update tt set x = 2;
end;
/
begin
update tt set x = x+1;
end;
/
commit;
drop table tt;
Trigger Which Modifies a Mutating Table
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>
SQL> CREATE OR REPLACE TRIGGER LimitMajors
2 BEFORE INSERT OR UPDATE OF major ON lecturer
3 FOR EACH ROW
4 DECLARE
5 studentMax CONSTANT NUMBER := 5;
6 studentCount NUMBER;
7 BEGIN
8 SELECT COUNT(*)
9 INTO studentCount
10 FROM lecturer
11 WHERE major = :new.major;
12
13 IF studentCount + 1 > studentMax THEN
14 RAISE_APPLICATION_ERROR(-20000,
15 "Too many lecturer in major " || :new.major);
16 END IF;
17 END LimitMajors;
18 /
Trigger 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> drop table lecturer;
Table dropped.
Use RAISE_APPLICATION_ERROR in a trigger
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 or replace trigger myTrigger
2 after delete or update or insert on emp
3 declare
4 begin
5 for irec in (select deptno, count(*) emps
6 from emp
7 group by deptno
8 having count(*) < 3 or count(*) > 8)
9 loop
10 RAISE_APPLICATION_ERROR(-20000, "Department "||irec.deptno || " has "||irec.emps||" emps!");
11 end loop;
12 end;
13 /
SQL>
SQL> drop table emp;
Use Sequence in a trigger
SQL>
SQL> create table foo(
2 id number primary key,
3 data varchar2(100) );
Table created.
SQL>
SQL> create sequence foo_seq;
Sequence created.
SQL>
SQL> create or replace trigger myTrigger
2 before insert
3 on foo
4 for each row
5 begin
6 select foo_seq.nextval
7 into :new.id
8 from dual;
9 end;
10 /
Trigger created.
SQL>
SQL> insert into foo ( data ) values ( "Christopher" );
1 row created.
SQL>
SQL> insert into foo ( id, data )values ( 5, "Sean" );
1 row created.
SQL>
SQL> select * from foo;
ID DATA
---------- ----------------------------------------------------------------------------------------------------
1 Christopher
2 Sean
SQL>
SQL> drop table foo;
Table dropped.
SQL> drop sequence foo_seq;
Sequence dropped.
SQL>
SQL>
Use sysdate and user function in a trigger
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> alter table dept add(last_update date,last_user varchar2(30));
Table altered.
SQL>
SQL> CREATE OR REPLACE TRIGGER deptBR
2 before update or insert
3 ON dept
4 FOR EACH ROW
5 DECLARE
6 begin
7 :new.last_update := sysdate;
8 :new.last_user := user;
9 end;
10 /
Trigger created.
SQL>
SQL> drop table dept;
Table dropped.