Mega Code Archive
Use cursor in trigger
<![CDATA[
SQL>
SQL> CREATE TABLE customer
2 (customer_id NUMBER(7),
3 customer_name VARCHAR2(50),
4 phone VARCHAR2(15),
5 address VARCHAR2(400),
6 city VARCHAR2(35),
7 state VARCHAR2(30),
8 country VARCHAR2(30),
9 zip_code VARCHAR2(10),
10 credit_rating VARCHAR2(9),
11 sales_rep_id NUMBER(7),
12 region_id NUMBER(7),
13 comments VARCHAR2(255),
14 preferred_customer VARCHAR2(1) DEFAULT 'N' NOT NULL,
15 shipping_method VARCHAR2(1) DEFAULT 'M' NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, 'Jane', '111-1111', '7 AVE','SAO', NULL, 'BRAZIL', NULL, 'EXCELLENT',12, 2, 'A', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (202, 'Todd', '222-2222', '6 BLVD.','OSAKA', NULL, 'JAPAN', NULL, 'POOR', 14, 4, 'B', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (203, 'Sharon', '333-3333', '1 STREET', 'NEW DELHI', NULL, 'INDIA', NULL, 'GOOD', 14, 4,'C', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (204, 'Hong', '444-4444', '2 STREET','SEATTLE', 'WASHINGTON', 'USA', '98101', 'EXCELLENT',11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (205, 'Anderson','555-5555', '5 ROAD', 'HONG KONG', NULL, NULL,NULL, 'EXCELLENT', 15, 4, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (206, 'Bob', '666-6666', '1 ROAD','CANNES', NULL, 'FRANCE', NULL, 'EXCELLENT', 15, 5,'D', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (207, 'Cat', '777-7777', '6 STREET','LAGOS', NULL, 'NIGERIA', NULL, 'GOOD', NULL, 3, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (208, 'Doge', '888-8888', '4 RASSE', 'STUTTGART', NULL, 'GERMANY', NULL, 'GOOD', 15, 5,'E', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (209, 'Black', '999-9999', '2 MAR','SAN PEDRO DE MACON''S', NULL, 'DOMINICAN REPUBLIC',NULL, 'EXCELLENT', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (210, 'Red', '000-0000', '3 ARO','NOGALES', NULL, 'MEXICO', NULL, 'EXCELLENT', 12, 2,'Customer is difficult to reach by phone. Try mail.','N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (211, 'Ted', '123-1231', '7 MOD', 'PRAGUE',NULL, 'CZECHOSLOVAKIA', NULL, 'EXCELLENT', 15, 5, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (212, 'Homas', '124-1234', '5 COR','ALEXANDRIA', NULL, 'EGYPT', NULL, 'EXCELLENT', 13, 3,'F', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (213, 'Look', '555-6281', '4 STREET', 'SAN FRANCISCO', 'CA', 'USA', '94117','EXCELLENT', 11, 1, 'G', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (214, 'Yellow', '555-7171', '4 STREET','BUFFALO', 'NY', 'USA', '14202', 'POOR', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (215, 'White', '337-3892', '6 YEK','SAINT PETERSBURG', NULL, 'RUSSIA', NULL, 'POOR',15, 5, 'T', 'N', 'M');
1 row created.
SQL>
SQL> CREATE TABLE ord
2 (order_id NUMBER(7),
3 customer_id NUMBER(7),
4 date_ordered DATE,
5 date_shipped DATE,
6 sales_rep_id NUMBER(7),
7 total NUMBER(11, 2),
8 payment_type VARCHAR2(6),
9 order_filled VARCHAR2(1)
10 );
Table created.
SQL>
SQL> INSERT INTO ord VALUES (100, 204, '31-AUG-92', '10-SEP-92', 11, 601100, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (101, 205, '31-AUG-92', '15-SEP-92', 14, 8056.6, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (102, 206, '01-SEP-92', '08-SEP-92', 15, 8335, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (103, 208, '02-SEP-92', '22-SEP-92', 15, 377, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (104, 208, '03-SEP-92', '23-SEP-92', 15, 32430, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (105, 209, '04-SEP-92', '18-SEP-92', 11, 2722.24, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (106, 210, '07-SEP-92', '15-SEP-92', 12, 15634, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (107, 211, '07-SEP-92', '21-SEP-92', 15, 142171, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (108, 212, '07-SEP-92', '10-SEP-92', 13, 149570, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (109, 213, '08-SEP-92', '28-SEP-92', 11, 1020935, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (110, 214, '09-SEP-92', '21-SEP-92', 11, 1539.13, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (111, 204, '09-SEP-92', '21-SEP-92', 11, 2770, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (112, 210, '31-AUG-92', '10-SEP-92', 12, 550, 'CREDIT', 'Y');
1 row created.
SQL>
SQL> CREATE OR REPLACE TRIGGER bi_order
2 BEFORE INSERT
3 ON ord
4 REFERENCING OLD AS OLD NEW AS NEW
5 FOR EACH ROW
6 WHEN (NEW.payment_type = 'CREDIT')
7 DECLARE
8 CURSOR cur_check_customer IS
9 SELECT 'x'
10 FROM customer
11 WHERE customer_id = :NEW.customer_id
12 AND credit_rating = 'POOR';
13 lv_temp_txt VARCHAR2(1);
14 lv_poor_credit_excep EXCEPTION;
15 BEGIN
16 OPEN cur_check_customer;
17 FETCH cur_check_customer INTO lv_temp_txt;
18 IF (cur_check_customer%FOUND) THEN
19 CLOSE cur_check_customer;
20 RAISE lv_poor_credit_excep;
21 ELSE
22 CLOSE cur_check_customer;
23 END IF;
24 EXCEPTION
25 WHEN lv_poor_credit_excep THEN
26 RAISE_APPLICATION_ERROR(-20111, 'Cannot process CREDIT ' ||
27 'order for a customer with a POOR credit rating.');
28 WHEN OTHERS THEN
29 RAISE_APPLICATION_ERROR(-20122, 'Unhandled error occurred in' ||
30 ' BI_ORDER trigger for order#:' || TO_CHAR(:NEW.ORDER_ID));
31 END bi_order;
32 /
Trigger created.
SQL>
SQL> show error
No errors.
SQL>
SQL> drop table customer;
Table dropped.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
]]>