Re: Database triggers - Mailing list pgsql-sql

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