Oracle PL/SQL Tutorial/Trigger/Utility trigger — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 13:45, 26 мая 2010

A trigger prevents updates after business hours

SQL>
SQL>
SQL> create table employee(
  2           emp_no                 integer     primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(5,2)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18        ,mgr              integer
 19        ,region           number
 20        ,division         number
 21        ,total_sales          number
 22         );
Table created.
SQL>
SQL>
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title)
  2  values (1,"Gardinia","Joy","R","688 Ave","New York","NY","12122","2333","212","200-3393","12-nov-1956","President");
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (2,"Anderson","Lucy","J","33 Ave","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1951","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values (3,"Somers","Ingrid","E","12 Ave","New York","NY","76822","8763","212","867-6893",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (4,"Washington","Georgia","J","13th Street","New York","NY","43122","4333","212","340-4365",11.50,"2-jul-1963","21-apr-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (5,"Doright","Dudley","J","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",21.65,"15-may-1958","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values ( 6,"Doright","Dorothy","R","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",24.65,"10-dec-1968","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 7,"Perry","Donna","R","1st Ave","New York","NY","44444","3444","212","111-6893",7.75,"14-feb-1967","15-mar-1995","Sales Clerk",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 8,"Roger","John","E","67 H Ave","New York","NY","33822","1163","212","122-6893",10.00,"14-jun-1956","15-mar-1995","Accountant",3,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 9,"Hall","Ted","R","1236 Lane","New York","NY","33823","1164","212","222-4393",13.00,"10-jun-1959","15-aug-1997","Sales Representative",3,1,100,10,50000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 10,"Barbee","Candice","L","400 Street","New York","NY","33811","2009","212","321-6873",12.00,"10-oct-1964","15-jan-1999","Sales Representative",3,1,100,10,35000);
1 row created.
SQL>
SQL> create or replace trigger tr_emp_maint_restrict
  2  before update or insert or delete
  3  on employee
  4  begin
  5    if to_char(sysdate, "hh24") >= "09" AND
  6       to_char(sysdate, "hh24") <= "17" then
  7       null;
  8    else
  9          raise_application_error (-20000, "Employee info may not be modified at this time!") ;
 10    end if ;
 11  end ;
 12  /
Trigger created.
SQL>
SQL> --to test
SQL> update employee set area_code = 212 where emp_no = 1;
update employee set area_code = 212 where emp_no = 1
       *
ERROR at line 1:
ORA-20000: Employee info may not be modified at this time!
ORA-06512: at "sqle.TR_EMP_MAINT_RESTRICT", line 6
ORA-04088: error during execution of trigger "sqle.TR_EMP_MAINT_RESTRICT"

SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>


Records a error in the error logging table.

SQL>
SQL>
SQL> CREATE SEQUENCE system_error_id NOCACHE;
Sequence created.
SQL>
SQL> CREATE TABLE system_errors
  2     (system_error_id  NUMBER(10,0), package_name  VARCHAR2(50),
  3      procedure_name  VARCHAR2(50), execution_location  varchar2(20),
  4      oracle_error_text  VARCHAR2(200),
  5      additional_information  VARCHAR2(2000),
  6      call_stack   VARCHAR2(2000), error_stack  VARCHAR2(2000),
  7      insert_time  DATE, insert_user  VARCHAR2(30));
Table created.
SQL>
SQL> COMMENT ON TABLE system_errors IS
  2     "Errors generated by stored packages.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.system_error_id IS
  2     "The system-wide ID to identify a system error. Useful for
  3      determining the order in which errors were encountered and
  4      logged.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.package_name IS "The package name.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.procedure_name IS "The procedure/function name.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.execution_location IS "A reference to a location in the executing code.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.oracle_error_text IS "The text of the Oracle error message.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.additional_information IS "Any pertinent information the developer may be trapping by the error handler.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.call_stack IS "The call stack at the time of the error.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.error_stack IS "The error stack at the time of the error.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.insert_time IS "The date and time of record insertion.";
Comment created.
SQL> COMMENT ON COLUMN system_errors.insert_user IS "The user inserting the record.";
Comment created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE log_error
  2     (p_package_txt   VARCHAR2 DEFAULT "UNKNOWN",
  3     p_procedure_txt VARCHAR2 DEFAULT "UNKNOWN",
  4     p_location_txt  VARCHAR2 DEFAULT "UNKNOWN",
  5     p_error_txt     VARCHAR2 DEFAULT "UNKNOWN",
  6     p_text_txt      VARCHAR2 DEFAULT "NONE",
  7     p_commit_bln    BOOLEAN  DEFAULT TRUE,
  8     p_user_txt      VARCHAR2 DEFAULT USER,
  9     p_time_date     DATE     DEFAULT SYSDATE) IS
 10     lv_call_stack_txt VARCHAR2(2000);
 11     lv_error_stack_txt VARCHAR2(2000);
 12     pu_failure_excep EXCEPTION;
 13     PRAGMA EXCEPTION_INIT (pu_failure_excep, -20000);
 14  BEGIN
 15     lv_call_stack_txt := SUBSTR(DBMS_UTILITY.FORMAT_CALL_STACK, 1, 2000);
 16     lv_error_stack_txt := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 2000);
 17     INSERT INTO system_errors (system_error_id, package_name, procedure_name,
 18         execution_location, oracle_error_text, additional_information,
 19         call_stack, error_stack, insert_time, insert_user)
 20     VALUES (system_error_id.NEXTVAL, SUBSTR(p_package_txt, 1, 50),
 21         SUBSTR(p_procedure_txt, 1, 50),
 22         SUBSTR(p_location_txt, 1, 20), SUBSTR(p_error_txt, 1, 200),
 23         SUBSTR(p_text_txt, 1, 2000), lv_call_stack_txt,
 24         lv_error_stack_txt, p_time_date, p_user_txt);
 25     IF p_commit_bln THEN
 26        COMMIT;
 27      END IF;
 28  EXCEPTION
 29     WHEN OTHERS THEN
 30        RAISE pu_failure_excep;
 31  END log_error;
 32  /
Procedure created.
SQL>
SQL> drop sequence system_error_id;
Sequence dropped.
SQL>
SQL>
SQL> drop table system_errors;
Table dropped.


Use trigger to keep data consistency

SQL>
SQL>
SQL> create table ord
  2          (
  3           order_no               integer         primary key
  4          ,empl_no                integer
  5          ,order_date             date not null
  6          ,total_order_price      number(7,2)
  7          ,deliver_date           date
  8          ,deliver_time           varchar2(7)
  9          ,payment_method         varchar2(2)
 10          ,emp_no                 number(3,0)
 11          ,deliver_name           varchar2(35)
 12          ,gift_message           varchar2(100)
 13           );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2  values(1,1,add_months(sysdate, -1), 235.00, "14-Feb-1999", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(2,1,add_months(sysdate, -2), 50.98, "14-feb-1999", "1 pm", "CA",7, "Rose", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(3, 2,add_months(sysdate, -3), 35.99, "14-feb-1999", "1 pm", "VS",2, "Ruby", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(4, 2,add_months(sysdate, -4), 19.95, "14-feb-1999", "5 pm", "CA",2, "Coy", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(7, 9,add_months(sysdate, -7), 35.95, "21-jun-1999", "12 noon", "VS", 2, "Fill", "Happy Birthday from Joe");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values (8, 12, add_months(sysdate, -8), 35.95, "1-jan-2000", "12 noon", "DI",3, "Laura", "Happy New Year""s from Lawrence");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values (9, 12, add_months(sysdate, -9), 75.95, "2-jan-2000", "12 noon", "CA",7, "Sara", "Happy Birthday from Lawrence" );
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(10, 4, add_months(sysdate, -10), 19.95, sysdate, "2:30 pm", "VG",2, "OK", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2  values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, "1:30 pm", "VG",2, "Hi", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2  values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, "3:30 pm", "CA",2, "Jack", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2  values(13, 7, add_months(sysdate, -1), 21.95, sysdate, "3:30 pm", "CA",2, "Jay", "Thanks for giving 100%!");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> create table ord_item
  2          (
  3           order_no               integer
  4          ,product_id             integer
  5          ,quantity               number(4,0)
  6          ,item_price                 number(7,2)
  7        ,total_order_item_price   number(9,2)
  8          ,primary key (order_no ,product_id)
  9          );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(1,   2,  10, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 1, 1, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 8, 1, 17.48 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(3, 8, 1, 35.99 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(4, 7, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(5, 5, 1, 10.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(6, 8, 1, 22.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 1, 6, 15.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 8, 1, 10.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(8, 8, 1, 35.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 8, 1, 65.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(10, 3, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger tr_total_order_price
  2  after
  3  update of item_price or insert or delete
  4  on ord_item
  5  for each row
  6  declare
  7      v_change_in_price  number;
  8      v_order_no number;
  9  begin
 10  if inserting then
 11      v_change_in_price := :new.item_price * :new.quantity ;
 12      v_order_no := :new.order_no;
 13      dbms_output.put_line("inserting");
 14  elsif updating then
 15      v_change_in_price := (:new.item_price * :new.quantity) - (:old.item_price * :old.quantity) ;
 16      v_order_no := :old.order_no;
 17      dbms_output.put_line("updating");
 18  else
 19      v_change_in_price := (:old.item_price * :old.quantity)  * -1;
 20      v_order_no := :old.order_no;
 21      dbms_output.put_line("deleting");
 22  end if;
 23
 24  update ord
 25  set total_order_price = total_order_price + v_change_in_price
 26  where order_no = v_order_no;
 27  end;
 28  /
Trigger created.
SQL>
SQL> update ord_item set item_price = 25 where order_no = 7 and product_id = 5 ;
1 row updated.
SQL>
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL> drop table ord_item;
Table dropped.
SQL>