PROGRAM 6 /* program: program6.sql author: anthony f. ortiz */ /* this program demonstrates an oracle pl/sql trigger. */ SQL> get hw6.sql 1 /* this pl/sql trigger fires when a row is inserted into the orders */ 2 /* table. it adds 1 to the percent of the agent who placed the */ 3 /* order. also, it adds 1 to the percent of any agent who is from */ 4 /* the same city. */ 5 create or replace trigger insert_orders 6 after insert on orders 7 for each row 8 begin 9 update agents 10 set percent = percent + 1 11 where city in (select city 12 from agents 13 where aid = :new.aid); 14* end; 15 / Trigger created. SQL> select * 2 from agents; AID ANAME CITY PERCENT --- ------------- -------------------- ---------- a01 Smith New York 6 a02 Jones Newark 6 a03 Brown Tokyo 7 a04 Gray New York 6 a05 Otasi Duluth 5 a06 Smith Dallas 5 6 rows selected. SQL> select * 2 from orders; ORDNO MON CID AID PID QTY DOLLARS ---------- --- ---- --- --- ---------- ---------- 1011 jan c001 a01 p01 1000 450 1012 jan c001 a01 p01 1000 450 1019 feb c001 a02 p02 400 180 1017 feb c001 a06 p03 600 540 1018 feb c001 a03 p04 600 540 1023 mar c001 a04 p05 500 450 1022 mar c001 a05 p06 400 720 1025 apr c001 a05 p07 800 720 1013 jan c002 a03 p03 1000 880 1026 may c002 a05 p03 800 704 1015 jan c003 a03 p05 1200 1104 ORDNO MON CID AID PID QTY DOLLARS ---------- --- ---- --- --- ---------- ---------- 1014 jan c003 a03 p05 1200 1104 1021 feb c004 a06 p01 1000 460 1016 jan c006 a01 p01 1000 500 1020 feb c006 a03 p07 600 600 1024 mar c006 a06 p01 800 400 16 rows selected. SQL> insert into orders (ordno, month, cid, aid, pid, qty, dollars) 2 values (1031, 'jul', 'c001', 'a01', 'p01', 1000, 450.00); 1 row created. SQL> select * 2 from agents; AID ANAME CITY PERCENT --- ------------- -------------------- ---------- a01 Smith New York 7 a02 Jones Newark 6 a03 Brown Tokyo 7 a04 Gray New York 7 a05 Otasi Duluth 5 a06 Smith Dallas 5 6 rows selected. SQL> select * 2 from orders; ORDNO MON CID AID PID QTY DOLLARS ---------- --- ---- --- --- ---------- ---------- 1011 jan c001 a01 p01 1000 450 1012 jan c001 a01 p01 1000 450 1019 feb c001 a02 p02 400 180 1017 feb c001 a06 p03 600 540 1018 feb c001 a03 p04 600 540 1023 mar c001 a04 p05 500 450 1022 mar c001 a05 p06 400 720 1025 apr c001 a05 p07 800 720 1013 jan c002 a03 p03 1000 880 1026 may c002 a05 p03 800 704 1015 jan c003 a03 p05 1200 1104 ORDNO MON CID AID PID QTY DOLLARS ---------- --- ---- --- --- ---------- ---------- 1014 jan c003 a03 p05 1200 1104 1021 feb c004 a06 p01 1000 460 1016 jan c006 a01 p01 1000 500 1020 feb c006 a03 p07 600 600 1024 mar c006 a06 p01 800 400 1031 jul c001 a01 p01 1000 450 17 rows selected
BACK TO CS4660 PAGE.