Hi

I am currently using Oracle 9i, using the Scott database and have a number of contraints which I need to implement. I have very little knowledge of creating triggers, but have had a go. However I would be grateful if someone could help me with the syntax, code, layout etc.

There are a number of them - for each one I have attempted to create a trigger, but am unsure of if they are correct or if they satisfy the criteria required.

Any help would be greatly appreciated.

These are the schema which I need to create triggers for:

Q1) No new orders are to be accepted from customers in the city of BURLINGAME.

 CREATE OR REPLACE TRIGGER check_custid
 BEFORE UPDATE OF custid ON ord
 FOR EACH ROW
 WHEN (city = 'BURLINGAME')
 BEGIN
   IF :city = 'BURLINGAME' THEN
 RAISE_APPLICATION_ERROR(-20101,'Cannot accept new order for city of BURLINGAME');
    ELSE DISPLAY(‘Update unsuccessful’);
   END IF;
 END;
 /

Q2) No deletions can leave an order without any associated items

CREATE OR REPLACE TRIGGER UpdateItemid
AFTER INSERT OR DELETE OR UPDATE on Ord

DECLARE

CURSOR c_Itemordid IS
    SELECT itemid
FROM Item
GROUP BY ordid;

BEGIN
 display ('Trigger delete_itemid in progress');

FOR v_ItemRow in c_Itemid LOOP
  DELETE itemid

Q3) Any new department based in ‘HOUSTON’ is to have the string ‘OIL’ somewhere in the department’s name.

 CREATE OR REPLACE TRIGGER check_dept
 BEFORE INSERT OR UPDATE OF loc ON dept
 FOR EACH ROW
 WHEN(new.dept <> ‘HOUSTON’)
 DECLARE
   v_OILdept.loc%TYPE;
 BEGIN
   SELECT loc,
   INTO v_loc
   FROM dept
   WHERE loc = :'HOUSTON';
   IF :deptname = 'OIL'
      RAISE_APPLICATION_ERROR(-20102’Update succeeded’);
   END IF;
 END;
 /

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.

CREATE OR REPLACE TRIGGER check_itemtot
AFTER UPDATE OF itemtot ON Item
DECLARE
  v_itemtot_changes NUMBER;
BEGIN
  SELECT upd, max_itemtot
  INTO v_itemtot_changes
  FROM audit_table


Q5) No individual user is allowed to update the products associated with a particular order more than twice

CREATE OR REPLACE TRIGGER secure_Ord
BEFORE INSERT OR UPDATE OR DELETE ON Ord
BEGIN
  IF (To_char(Ordid, 'prodid') IN ('ITEM', 'ORD')
  THEN
   IF DELETING
       THEN RAISE_APPLICATION_ERROR(-20103, You may only update
                  the products more than twice');

   END IF
  ENDIF;
END;
/

Q6) Any update or insert into the SALGRADE table has to ensure that salary ranges for each grade do not overlap.

CREATE OR REPLACE TRIGGER check_salary_count
AFTER UPDATE OR INSERT OF grade ON salgrade
DECLARE
  v__grade_changes NUMBER
  v_sal_changes NUMBER;
BEGIN
  SELECT upd, max_upd
  INTO v_grade_changes, v_max_changes
  FROM audit_table
  WHERE user_name = user
  AND tablename = ‘SALGRADE’
  AND column_name = ‘GRADE’

  IF v_salary_changes > v_max_changes THEN
  RAISE_APPLICATION_ERROR(-20104, ‘Salary Ranges for each grade must not overlap')
  END IF;
END;
/

Any help would be gratefully received.

Thanks

Mo.

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