Thread: Re: Database triggers

Do you have access to the SQL Reference Manual:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmsthctri
gger.htm#HDRHCTRIGGER


There's an example of what you want to do:

CREATE TRIGGER SAL_ADJ
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING OLD AS OLD_EMP           NEW AS NEW_EMP
FOR EACH ROW MODE DB2SQL
WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20))
BEGIN ATOMICSIGNAL SQLSTATE ?75001?(?Invalid Salary Increase - Exceeds 20%?);
END

Since you want a before trigger change "AFTER UPDATE" to "BEFORE UPDATE"

Note: You can't have a single trigger that fires before INSERT, UPDATE,
and DELETE.  At least not when defined via SQL ;-)  So you'll have to
have 3 separate statements.

HTH,
Charles


In article <2590fb58.0404140557.71480015@posting.google.com>,
cmpofu@iupui.edu says...
> 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);
>

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
DECLAREtoday_date date;part_cost number(8, 2);current_status char(1);future_date exception;high_cost
exception;discount_errorexception;invalid_insert exception;invalid_status exception;delete_status exception;
 

BEGIN
if inserting or updating thentoday_date := :new.QUOTE_DATE;if today_date > SYSDATE then    raise future_date;end
if;selectPART.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 thenif upper(:NEW.STATUS) != 'P' then    raise invalid_insert;end if;
end if;
if updating thenif upper(:NEW.STATUS) != 'A' then    raise invalid_status;end if;
end if;
if deleting thenselect 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;

EXCEPTIONwhen 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;