Hi Monique,
I tested out some of your triggers in Oracle 9.0.1.1.1 ( i.e. 9i Release 1 ) hence some of the demo tables/columns were not present.
There are pretty basic errors in the code. I am jotting down some of the triggers that I developed:
************************************************** ********************
Q1) No new orders are to be accepted from customers in the city of BURLINGAME. ( I tried this with "Blueville", since it was existing in my database ).
SQL> CREATE OR REPLACE TRIGGER check_custid
2 BEFORE INSERT ON ord
3 FOR EACH ROW
4 DECLARE
5 v_address_type t_address;
6 e_reject_customer exception;
7 BEGIN
8 select c.address
9 into v_address_type
10 from customer c
11 where c.id = :new.customer_id;
12 IF (v_address_type.city = 'Blueville') THEN
13 raise e_reject_customer ;
14 END IF;
15 dbms_output.put_line('Insert successful');
16 EXCEPTION
17 WHEN e_reject_customer THEN
18 RAISE_APPLICATION_ERROR(-20101,'Cannot accept new order for city of Blueville.');
19 WHEN OTHERS THEN
20 dbms_output.put_line('Error in trigger check_custid : '||sqlerrm);
21 END;
22 /
Trigger created.
SQL> -- check the customer's address for id = 201
SQL> select id, address from customer where id = 201 ;
ID
----------
ADDRESS(STREET, CITY, STATE, ZIPCODE)
----------------------------------------------------------------------------------------------------
201
T_ADDRESS('101 California', 'Blueville', 'CA', '94036-3209')
SQL> -- this should fail since 201 is for a customer in 'Blueville'
SQL> insert into ord ( CONTACTNAME, STATUS, CUSTOMER_ID )
2 values ( 'Monique', 'P', 201 );
insert into ord ( CONTACTNAME, STATUS, CUSTOMER_ID )
*
ERROR at line 1:
ORA-20101: Cannot accept new order for city of Blueville.
ORA-06512: at "TEST.CHECK_CUSTID", line 15
ORA-04088: error during execution of trigger 'TEST.CHECK_CUSTID'
SQL> -- check the customer's address for id = 202
SQL> select id, address from customer where id = 202 ;
ID
----------
ADDRESS(STREET, CITY, STATE, ZIPCODE)
----------------------------------------------------------------------------------------------------
202
T_ADDRESS('420 Redmond Way', 'Greenville', 'CA', '94112-8724')
SQL> -- this should succeed since 202 is for a customer in 'Greenville'
SQL> insert into ord ( CONTACTNAME, STATUS, CUSTOMER_ID )
2 values ( 'Monique', 'P', 202 );
Insert successful
1 row created.
SQL> commit;
Commit complete.
SQL>
************************************************** ********************
Q2) No deletions can leave an order without any associated items
Not sure what the requirement is, but here's what I could figure out:
The LINE_ITEM table is a child of ORD and we have a one-to-many relationship between an order id
and line_item id.
- When a record from the ORD table is deleted, Oracle will normally throw an
error if there are child records present in the LINE_ITEM table for that order id.
- If no child records are present, the order id will be deleted successfully from the ORD table.
If you want Oracle to delete all child records (in LINE_ITEM) when the parent order id is deleted,
then you will have to recreate the foreign key constraint in LINE_ITEM table with the
"ON DELETE CASCADE" clause:
ALTER TABLE LINE_ITEM DROP CONSTRAINT ITEM_ORDERED_ON ;
ALTER TABLE LINE_ITEM ADD CONSTRAINT ITEM_ORDERED_ON FOREIGN KEY ( ORD_ID )
REFERENCES ORD ( ID ) ON DELETE CASCADE ;
Then, deletion of any order id from the ORD table will first delete the child records from
LINE_ITEM table.
************************************************** ********************
Q3) Any new department based in âHOUSTONâ is to have the string âOILâ somewhere in the departmentâs name.
SQL> CREATE OR REPLACE TRIGGER check_dept
2 BEFORE INSERT OR UPDATE ON dept
3 FOR EACH ROW
4 WHEN ( new.loc = 'HOUSTON' )
5 DECLARE
6 e_invalid_deptname exception;
7 BEGIN
8 if ( upper(:new.dname) not like '%OIL%' ) then
9 raise e_invalid_deptname;
10 end if;
11 EXCEPTION
12 WHEN e_invalid_deptname THEN
13 RAISE_APPLICATION_ERROR(-20102,'A department located in HOUSTON must have "OIL" somewhere
in its name.');
14 WHEN OTHERS THEN
15 dbms_output.put_line('Error in trigger check_dept : '||sqlerrm);
16 END;
17 /
Trigger created.
SQL> -- check the data in DEPT table
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> -- try to insert a record in DEPT table with location 'HOUSTON'
SQL> -- and name 'ADVERTISING'
SQL> insert into dept ( deptno, dname, loc )
2 values ( 50, 'ADVERTISING', 'HOUSTON' );
insert into dept ( deptno, dname, loc )
*
ERROR at line 1:
ORA-20102: A department located in HOUSTON must have "OIL" somewhere in its name.
ORA-06512: at "TEST.CHECK_DEPT", line 9
ORA-04088: error during execution of trigger 'TEST.CHECK_DEPT'
SQL> -- now try to insert a record in DEPT table with location 'HOUSTON'
SQL> -- and name 'BOILER'
SQL> insert into dept ( deptno, dname, loc )
2 values ( 50, 'BOILER', 'HOUSTON' );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- view records
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 BOILER HOUSTON
SQL>
SQL>
************************************************** ********************
Q4) The itemtot attribute (the inclusion of which in the database is evidence of very bad design) needs to be
automatically maintained and not be directly updateable.
Not sure what is being required here.
************************************************** ********************
Q5) No individual user is allowed to update the products associated with a particular order more than twice
I think you need to store the information of the user who is updating the records in the ORD table. Probably setting the Auditing features of Oracle on would help.
************************************************** ********************
Q6) Any update or insert into the SALGRADE table has to ensure that salary ranges for each grade do not overlap.
SQL> -- a simple test for overlap of grades would be to see if
SQL> -- 1. the "LOSAL" of the record being inserted/updated does not lie between LOSAL or HISAL
SQL> -- of any of the existing records.
SQL> -- 2. the "HISAL" of the record being inserted/updated does not lie between LOSAL or HISAL
SQL> -- of any of the existing records
SQL>
SQL> CREATE OR REPLACE TRIGGER check_salary_count
2 BEFORE UPDATE OR INSERT OF grade ON salgrade
3 FOR EACH ROW
4 DECLARE
5 v_grade salgrade.grade%type;
6 e_range_overlap exception;
7 BEGIN
8 select grade
9 into v_grade
10 from salgrade
11 where (
12 ( :new.losal between losal and hisal ) or
13 ( :new.hisal between losal and hisal )
14 );
15 -- we got one overlapping record; raise the exception
16 raise e_range_overlap ;
17
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 dbms_output.put_line('The grade information is valid');
21 WHEN TOO_MANY_ROWS THEN -- more than one overlapping record
22 RAISE_APPLICATION_ERROR(-20104, 'Salary Ranges for each grade must not overlap');
23 WHEN e_range_overlap THEN
24 RAISE_APPLICATION_ERROR(-20105, 'Salary Ranges for each grade must not overlap');
25 WHEN OTHERS THEN
26 dbms_output.put_line('Error in trigger check_salary_count : '||sqlerrm);
27 END;
28 /
Trigger created.
SQL> -- check the data in the SALGRADE table
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL>
SQL> -- insert a record with losal = 2500
SQL> insert into salgrade ( grade, losal, hisal )
2 values ( 6, 2500, 3500 ) ;
insert into salgrade ( grade, losal, hisal )
*
ERROR at line 1:
ORA-20104: Salary Ranges for each grade must not overlap
ORA-06512: at "TEST.CHECK_SALARY_COUNT", line 19
ORA-04088: error during execution of trigger 'TEST.CHECK_SALARY_COUNT'
SQL>
SQL> -- insert a record with losal = 10000 and hisal = 1500
SQL> insert into salgrade ( grade, losal, hisal )
2 values ( 6, 10000, 1500 ) ;
insert into salgrade ( grade, losal, hisal )
*
ERROR at line 1:
ORA-20105: Salary Ranges for each grade must not overlap
ORA-06512: at "TEST.CHECK_SALARY_COUNT", line 21
ORA-04088: error during execution of trigger 'TEST.CHECK_SALARY_COUNT'
SQL>
SQL> -- insert a record with proper values
SQL> insert into salgrade ( grade, losal, hisal )
2 values ( 6, 10000, 11000 ) ;
The grade information is valid
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
6 10000 11000
6 rows selected.
SQL>
SQL>
************************************************** ********************
Hope that helps. There's a classic book by Steve Fueurstein called "Oracle PL/SQL Programming" ( O'Reilly Press ) which you may want to refer for getting in-depth knowledge of Oracle programming.
With regards,
azizmasih
|