Oracle PL/SQL/Trigger/Create Trigger
Содержание
- 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
<source lang="sql">
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>
</source>
Cascade inserts into myStudent into session and lecturer.
<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> 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.
</source>
create or replace trigger
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "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>
</source>
Create tigger on wrapper table
<source lang="sql">
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> --
</source>
Creating a Trigger with cursor inside
<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> 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>
</source>
Empty trigger(before insert or update or delete)
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "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>
</source>
Oracle"s syntax for creating a trigger based on two tables
<source lang="sql">
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>
</source>
Show errors for a trigger
<source lang="sql">
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.
</source>
Submit job from a trigger
<source lang="sql">
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.
</source>
This trigger sends messages over a pipe to record inserts into myStudent.
<source lang="sql">
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;
</source>
Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
<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> 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>
</source>
Trigger on each row
<source lang="sql">
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;
</source>
Trigger Which Modifies a Mutating Table
<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> 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.
</source>
Use RAISE_APPLICATION_ERROR in a trigger
<source lang="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 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;
</source>
Use Sequence in a trigger
<source lang="sql">
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>
</source>
Use sysdate and user function in a trigger
<source lang="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> 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.
</source>
