From: Charles Wilt <cwilt@meaa.mea.com>
Subject: Re: Database triggers
Message-ID: <MPG.1ae85cb4749764d29896d8@news.easynews.com>
References: <2590fb58.0404140557.71480015@posting.google.com>
Organization: Mitsubishi Electric
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Lines: 192
Date: Thu, 15 Apr 2004 13:50:09 GMT
To: pgsql-sql@postgresql.org

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,=20
and DELETE.  At least not when defined via SQL ;-)  So you'll have to=20
have 3 separate statements.

HTH,
Charles


In article <2590fb58.0404140557.71480015@posting.google.com>,=20
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.
>=20
> THIS IS THE LAB:
>=20
> 1. Create a table called QUOTE.=20=20
> =B7	Give the table an initial and next extent size of 8192
> =B7	Specify a pctincrease of 0
> =B7	Define the following columns using the datatypes and length listed=20=
=20
> 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)
> =B7	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.
>=20
> 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.
>=20
> For example, a foreign key on the QUOTE table referring to the PART
> table should be called QUOTE_PART_FK.
>=20
> =B7	A foreign key on the SALES_REP_ID column should refer to the
> EMPLOYEE table.
> =B7	A foreign key on the CUST_NBR column should refer to the CUSTOMER
> table.
> =B7	A foreign key on the PART column should refer to the PART table.
>=20
> 3. Create a composite index on the CUST_NBR, PART and QUOTE_DATE
> columns.
> =B7	Give the index an initial and next extent of 8192
> =B7	Use pctincrease 0
> =B7	Name the index whatever you'd like
>=20
> 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:
>=20
> =B7	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
> =B7	If INSERTING, in addition to the rules listed above:
> o	STATUS must contain a value of  P (which stands for pending)
> =B7	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
> =B7	If DELETING
> o	STATUS must be P or C
>=20
> 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:
>=20
> =B7	Quote date can't be a future date
> =B7	Quoted price is too high
> =B7	New quotes must have a status of P
> =B7	Pending status (P) can only be changed to Approved (A)
> =B7	Invalid status code
> =B7	Won, Lost or Cancelled quotes can only be changed to Pending
>=20
> 5. Create a BEFORE UPDATE trigger on the PART table. The trigger
> should enforce the following rule:
> =B7	If UNIT_COST is being updated=09
> 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
>=20
> 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.
>=20
> 6. Write a series of statements to test your new triggers:
> =B7	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.
> =B7	Try to insert a row into the quote table with a price greater than
> that listed for the part in the PART table
> =B7	Try to insert a row into the quote table with a quantity > 100 and a
> price > 20% off the price in the PART table
> =B7	Try to INSERT a row with a STATUS other than P
> =B7	Now insert a valid row so that you can test some UPDATE statements
> =B7	Issue an UPDATE to modify the price to a price higher than that in
> the PART table
> =B7	Issue an UPDATE to modify the quote date to SYSDATE+1
> =B7	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
> =B7	Issue an update to modify the status from P to W
> =B7	Now issue a valid update to change the status to A
> =B7	Issue a delete to make sure you can't delete a row with status of A
> =B7	Finally, issue an update on the PART table to set the price higher
> than the quoted price in the QUOTE table
>=20
> THIS IS WHAT I HAVE DONE THUS FAR:
>=20
> set serveroutput on;
>=20
> 1.
>=20
> 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);=20=20
>=20
> ALTER TABLE QUOTE
> ADD CONSTRAINT ID_PK
> PRIMARY KEY (ID);
>=20
> 2.
>=20
> ALTER TABLE QUOTE
> ADD CONSTRAINT SALES_REP_ID_FK
> FOREIGN KEY (SALES_REP_ID)
> REFERENCES EMPLOYEE;
>=20
> ALTER TABLE QUOTE
> ADD CONSTRAINT CUST_NBR_FK
> FOREIGN KEY (CUST_NBR)
> REFERENCES CUSTOMER;
>=20
> ALTER TABLE QUOTE
> ADD CONSTRAINT PART_FK
> FOREIGN KEY (PART)
> REFERENCES PART;
>=20
> 3.
>=20
> CREATE INDEX QUOTEINDEX
> ON QUOTE (CUST_NBR, PART, QUOTE_DATE)
> STORAGE (INITIAL 8192K
>          NEXT 8192K
>          PCTINCREASE 0);
>=20