[SQL] Database triggers

[Charity M]

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_IDNUMBER(4)
o   CUST_NBRNUMBER(5)
o   PARTVARCHAR2(20)
o   QUANTITYNUMBER(4)
o   UNIT_COST   NUMBER(8,2)
o   STATUS  CHAR(1)
o   COMMENTSVARCHAR2(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
·   Fina

Re: [SQL] Database triggers

[Charity M]

Thank you, will look at the reference manual.  This is how I've done
it to this point.  It runs and the trigger is created but am not quite
sure if its the right thing.

CREATE OR REPLACE TRIGGER QUOTE_TRG
BEFORE INSERT or DELETE OR UPDATE ON QUOTE
FOR EACH ROW
DECLARE
today_date date;
part_cost number(8, 2);
current_status char(1);
future_date exception;
high_cost exception;
discount_error exception;
invalid_insert exception;
invalid_status exception;
delete_status exception;

BEGIN
if inserting or updating then
today_date := :new.QUOTE_DATE;
if today_date > SYSDATE then
raise future_date;
end if;
select PART.UNIT_COST into part_cost from PART where PART.PART_NBR =
:NEW.PART;
if part_cost < :NEW.UNIT_COST then
raise high_cost;
end if;

if :NEW.QUANTITY > 100 then
if (part_cost * .8) < :NEW.UNIT_COST then
raise discount_error;
end if;
end if;
end if;
if inserting then
if upper(:NEW.STATUS) != 'P' then
raise invalid_insert;
end if;
end if;
if updating then
if upper(:NEW.STATUS) != 'A' then
raise invalid_status;
end if;
end if;
if deleting then
select QUOTE.STATUS into current_status from QUOTE where QUOTE.ID =
:NEW.ID;
if current_status != 'P' and current_status != 'C' then
raise delete_status;
end if;
end if;

EXCEPTION
when future_date then
raise_application_error(-20110, 'Quote date cannot be a future
date.');
when high_cost then
raise_application_error(-20111, 'Quoted price is too high');
when discount_error then
raise_application_error(-20112, 'Quoted discount price is too
high');
when invalid_insert then
raise_application_error(-20113, 'New quotes must have a status of
P');
when invalid_status then
raise_application_error(-20114, 'Pending status (P) con only be
changed to Approved (A)');
when delete_status then
raise_application_error(-20115, 'Status must be (P) Pending or (C)
Cancelled to be deleted');


END;

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org