Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Trigger

 

Building INSTEAD OF trigger views

With an INSTEAD OF trigger you can define the behavior of INSERT, UPDATE, and DELETE for any view no matter how complex they are. SQL> SQL> -- create demo table SQL> create table Employee(   2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,   3    First_Name         VARCHAR2(10 BYTE),   4    Last_Name          VARCHAR2(10 BYTE),   5    Start_Date         DATE,   6    End_Date           DATE,   7    Salary             Number(8,2),   8    City               VARCHAR2(10 BYTE),   9    Description        VARCHAR2(15 BYTE)  10  )  11  / Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')   3  / 1 row created. SQL> SQL> -- display data in the table SQL> select * from Employee   2  / ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer 02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester 03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester 04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager 05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester 06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester 07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager 08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester 8 rows selected. SQL> SQL> create or replace view employee_view   2  as   3  select id, last_name, first_name   4  from employee   5  where id < 5   6  order by first_name; View created. SQL> / View created. SQL> SQL> select * from employee_view   2  / ID   LAST_NAME            FIRST_NAME ---- -------------------- -------------------- 02   Mathews              Alison 04   Rice                 Celia 03   Smith                James 01   Martin               Jason 4 rows selected. SQL> SQL> create or replace trigger employee_view_trigger   2  instead of delete on employee_view   3  referencing new as new old as old   4  begin   5      delete from employee   6      where id=:old.id;   7  end;   8  / Trigger created. SQL> SQL> delete from employee_view; 4 rows deleted. SQL> SQL> select * from employee; ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester 06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester 07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager 08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester 4 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee   2  / Table dropped. SQL>