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 ATOMIC
SIGNAL 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(dot)0404140557(dot)71480015(at)posting(dot)google(dot)com>,
cmpofu(at)iupui(dot)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);
>