Oracle PL/SQL/Trigger/Create Trigger

Материал из SQL эксперт
Версия от 09:58, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Trigger/Create_Trigger&oldid=1614»