🚀 go-pugleaf

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.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);
>