Database triggers - Mailing list pgsql-sql

I have a lab assignment that I have been struggling with.  We are
using oracle sql.  Can someone please help me.  See the lab below.  I
have done ques 1 - 3 thus far and am now stuck on triggers ques 4 - 6.

THIS IS THE LAB:

1. Create a table called QUOTE.  
·    Give the table an initial and next extent size of 8192
·    Specify a pctincrease of 0
·    Define the following columns using the datatypes and length listed  
below.  All columns should be mandatory except the COMMENTS column:
o    ID            NUMBER(4)
o    QUOTE_DATE    DATE
o    SALES_REP_ID    NUMBER(4)
o    CUST_NBR        NUMBER(5)
o    PART            VARCHAR2(20)
o    QUANTITY        NUMBER(4)
o    UNIT_COST        NUMBER(8,2)
o    STATUS        CHAR(1)
o    COMMENTS        VARCHAR2(100)
·    Define the ID column as the primary key for the table. You can do
this in the CREATE TABLE statement, or issue an ALTER TABLE statement
afterwards.

2. Alter the table above to add some foreign key constraints.  Name
these constraints QUOTE_tablename_FK, where tablename is the table
referred to by the foreign key.

For example, a foreign key on the QUOTE table referring to the PART
table should be called QUOTE_PART_FK.

·    A foreign key on the SALES_REP_ID column should refer to the
EMPLOYEE table.
·    A foreign key on the CUST_NBR column should refer to the CUSTOMER
table.
·    A foreign key on the PART column should refer to the PART table.

3. Create a composite index on the CUST_NBR, PART and QUOTE_DATE
columns.
·    Give the index an initial and next extent of 8192
·    Use pctincrease 0
·    Name the index whatever you'd like

4. Create a database trigger on the QUOTE table that will fire before
an INSERT, UPDATE or DELETE operation.  Name the trigger QUOTE_TRG.
The trigger should enforce the following rules:

·    If INSERTING or UPDATING
o    QUOTE_DATE cannot be greater that SYSDATE (the current system date
and time)
o    UNIT_COST can't be greater than the UNIT_COST for this part in the
PART table
o    If QUANTITY is over 100, the UNIT_COST must be at least 20% less
than the UNIT_COST for this part as listed in the PART TABLE
·    If INSERTING, in addition to the rules listed above:
o    STATUS must contain a value of  P (which stands for pending)
·    If UPDATING, in addition to the rules listed earlier:
o    A STATUS of P can only be changed to a STATUS of A (which stands for
active)
o    A STATUS of A can be changed to P, W, L or C (for pending, won, lost
or cancelled)
o    A STATUS of W, L or C can only be changed back to P
·    If DELETING
o    STATUS must be P or C

If any of these rules are violated, raise one of the following
exceptions which you will define in the EXCEPTION portion of your
trigger.  Raise an application error. Use whatever error numbers you'd
like, and provide meaningful text to describe the error:

·    Quote date can't be a future date
·    Quoted price is too high
·    New quotes must have a status of P
·    Pending status (P) can only be changed to Approved (A)
·    Invalid status code
·    Won, Lost or Cancelled quotes can only be changed to Pending

5. Create a BEFORE UPDATE trigger on the PART table. The trigger
should enforce the following rule:
·    If UNIT_COST is being updated    
o    The new price can't be lower than any of the quoted prices in the
QUOTE table for this part, if the quote status is P or A
o    The new price must be at least 20% more than any quoted prices in
the QUOTE table for this part, if the quote is for a quantity > 100
and the quote status is P or A

Define a single exception that is raised when either error occurs. The
text of the application error should indicate that the cost is invalid
based upon outstanding quotes on the part.

6. Write a series of statements to test your new triggers:
·    Try to insert a row into the quote table. For the quote date,
provide a value of SYSDATE+1. This will try to insert a row with
tomorrow's date for the quote date.
·    Try to insert a row into the quote table with a price greater than
that listed for the part in the PART table
·    Try to insert a row into the quote table with a quantity > 100 and a
price > 20% off the price in the PART table
·    Try to INSERT a row with a STATUS other than P
·    Now insert a valid row so that you can test some UPDATE statements
·    Issue an UPDATE to modify the price to a price higher than that in
the PART table
·    Issue an UPDATE to modify the quote date to SYSDATE+1
·    Issue an UPDATE to modify the quantity to > 100 and the price to
something higher than 20% off the price listed in the PART table
·    Issue an update to modify the status from P to W
·    Now issue a valid update to change the status to A
·    Issue a delete to make sure you can't delete a row with status of A
·    Finally, issue an update on the PART table to set the price higher
than the quoted price in the QUOTE table

THIS IS WHAT I HAVE DONE THUS FAR:

set serveroutput on;

1.

CREATE table QUOTE
(ID NUMBER(4),
QUOTE_DATE DATE,
SALES_REP_ID NUMBER(4),
CUST_NBR NUMBER(5),
PART VARCHAR2(20),
QUANTITY NUMBER(4),
UNIT_COST NUMBER(8,2),
STATUS CHAR(1),
COMMENTS VARCHAR2(100))
Storage (INITIAL 8K        NEXT 8k        PCTINCREASE 0);  

ALTER TABLE QUOTE
ADD CONSTRAINT ID_PK
PRIMARY KEY (ID);

2.

ALTER TABLE QUOTE
ADD CONSTRAINT SALES_REP_ID_FK
FOREIGN KEY (SALES_REP_ID)
REFERENCES EMPLOYEE;

ALTER TABLE QUOTE
ADD CONSTRAINT CUST_NBR_FK
FOREIGN KEY (CUST_NBR)
REFERENCES CUSTOMER;

ALTER TABLE QUOTE
ADD CONSTRAINT PART_FK
FOREIGN KEY (PART)
REFERENCES PART;

3.

CREATE INDEX QUOTEINDEX
ON QUOTE (CUST_NBR, PART, QUOTE_DATE)
STORAGE (INITIAL 8192K        NEXT 8192K        PCTINCREASE 0);