Oracle PL/SQL Tutorial/Trigger/Inserting
If new value is null use the value from a sequence
<source lang="sql">
SQL> SQL> SQL> create table myTable (id number); Table created. SQL> SQL> create sequence wo_seq;
SQL> SQL> create or replace trigger trg_id before insert
2 on myTable 3 for each row 4 when (new.id is null) 5 begin 6 select wo_seq.nextval into :new.id from dual; 7 end; 8 /
Trigger created. SQL> SQL> drop table myTable; Table dropped. SQL></source>
React to Inserting and Updating actions
<source lang="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> create or replace trigger tr_total_order_price
2 before 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 11 if inserting then 12 :new.total_order_item_price := :new.item_price * :new.quantity ; 13 14 v_change_in_price := :new.item_price * :new.quantity; 15 16 v_order_no := :new.order_no; 17 18 dbms_output.put_line("inserting"||:new.total_order_item_price); 19 20 elsif updating then 21 22 :new.total_order_item_price := :new.item_price * :new.quantity ; 23 24 v_change_in_price := (:new.item_price * :new.quantity) - (:old.item_price * :old.quantity) ; 25 26 v_order_no := :old.order_no; 27 28 dbms_output.put_line("updating"); 29 30 else 31 32 v_change_in_price := (:old.item_price * :old.quantity) * -1; 33 34 v_order_no := :old.order_no; 35 36 dbms_output.put_line("deleting"); 37 38 end if; 39 40 update ord 41 set total_order_price = total_order_price + v_change_in_price 42 where order_no = v_order_no; 43 44 end; 45 /
Trigger created. SQL> show errors No errors. SQL> SQL> select total_order_price from ord where order_no = 7; TOTAL_ORDER_PRICE
35.95
SQL> select total_order_item_price from ord_item
2 where order_no = 7 and product_id = 6;
no rows selected SQL> SQL> insert into ord_item (order_no, product_id, quantity, item_price)
2 values (7, 6, 2, 5) ;
1 row created. SQL> update ord_item set item_price = 25 where order_no = 7 and product_id = 5 ; 1 row updated. SQL> select total_order_price from ord where order_no = 7; TOTAL_ORDER_PRICE
60.45
SQL> select total_order_item_price from ord_item
2 where order_no = 7 and product_id = 6;
TOTAL_ORDER_ITEM_PRICE
10
SQL> SQL> SQL> drop table ord; Table dropped. SQL> SQL> drop table ord_item; Table dropped.</source>
Trigger that logs actions for inserting actions
<source lang="sql">
SQL> SQL> SQL> SQL> create table employee
2 ( 3 emp_no integer primary key 4 ,lastname varchar2(20) not null 5 ,firstname varchar2(15) not null 6 ,midinit varchar2(1) 7 ,street varchar2(30) 8 ,city varchar2(20) 9 ,state varchar2(2) 10 ,zip varchar2(5) 11 ,zip_4 varchar2(4) 12 ,area_code varchar2(3) 13 ,phone varchar2(8) 14 ,salary number(5,2) 15 ,birthdate date 16 ,hiredate date 17 ,title varchar2(20) 18 ,dept_no integer 19 ,mgr integer 20 ,region number 21 ,division number 22 ,total_sales number 23 );
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> SQL> SQL> CREATE TABLE logit( event VARCHAR2(400) ); Table created. SQL> SQL> SQL> create or replace trigger tr_trans_log
2 BEFORE INSERT OR UPDATE OR DELETE ON employee 3 DECLARE 4 v_msg varchar2(200) ; 5 PRAGMA AUTONOMOUS_TRANSACTION; 6 BEGIN 7 IF inserting THEN 8 v_msg := " Insert into employee @ " || to_char(sysdate, "mm/dd/yyyy hh:mi") || " by " || user ; 9 ELSIF updating THEN 10 v_msg := " Update of employee @ " || to_char(sysdate, "mm/dd/yyyy hh:mi") || " by " || user ; 11 ELSIF deleting THEN 12 v_msg := " Delete of employee @ " || to_char(sysdate, "mm/dd/yyyy hh:mi") || " by " || user ; 13 END IF; 14 INSERT INTO logit VALUES(v_msg); 15 COMMIT; 16 END; 17 /
Trigger created. SQL> SQL> drop table logit; Table dropped. SQL> SQL> drop table employee; Table dropped.</source>