Home » SQL & PL/SQL » SQL & PL/SQL » CREATE A TRIGGER (ORACLE 10.2.0,WIN 2000)
CREATE A TRIGGER [message #283179] Mon, 26 November 2007 05:34 Go to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

hi guys

i need to create a trigger that will add the sum of(amount) grouping by vendor_id

SQL> desc TBL_PROC_SPEND
 Name                                Null?    Type
 ----------------------------------- -------- ----------
 BUSINESS_UNIT_ID                             NUMBER
 AMOUNT_PAID                                  NUMBER
 VENDOR_ID                                    NUMBER
 PAY_DATE                                     DATE
 PROC_SPEND_ID                       NOT NULL NUMBER


and update the TOTAL_PROC_SPEND in the vendor table

SQL> desc tbl_vendor
 Name                                Null?    Type
 ----------------------------------- -------- --------------------
 VENDOR_ID                           NOT NULL NUMBER
 VENDOR_VERIF_START_DATE                      DATE
 VENDOR_VERIF_END_DATE                        DATE
 VENDOR_NAME                                  VARCHAR2(50)
 HOLDING_CO                                   VARCHAR2(50)
 HOLDING_CO_PCT                               FLOAT(126)
 DATE_EST                                     DATE
 SUPPLIER_TYPE                                VARCHAR2(50)
 VERIFICATION_AGENCY                          VARCHAR2(50)
 AUDIT_DATE                                   DATE
 SPEND_CLASSIFICATION_COMMENT                 VARCHAR2(50)
 INCLUDED_PROCURE_SPEND_PEC                   NUMBER
 SPEND_CLASSIFICATION_ID                      NUMBER
 ENT_SIZE_ID                                  VARCHAR2(1)
 VENDOR_SOURCE_ID                             NUMBER
 BUSINESS_UNIT_ID                             NUMBER
 VENDOR_CONTRI_LEVEL_ID                       NUMBER
 VENDOR_SERVICE_CAT_ID                        NUMBER
 BEE_DOCUMENT_ID                              NUMBER
 VENDOR_PROCURE_SPEND_ID                      NUMBER
 TOTAL_PROC_SPEND                             NUMBER(20)

SQL> 


it the a way i could capture the sum(amount) per vendor_id and update the table tbl_pro_spend TOTAL_PROC_SPEND every time the tbl_proc_spend is altered/
if anyone has a simple way of doing this, i'd be gratefull to try it.


thanks.
rebeccah
Re: CREATE A TRIGGER [message #283192 is a reply to message #283179] Mon, 26 November 2007 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a procedure; do not use a trigger for this otherwise you will have data inconsistency.
In addition, you have to lock the parent table row (select for update) each time you want to update/add/delete a child one.

Regards
Michel

Re: CREATE A TRIGGER [message #283195 is a reply to message #283192] Mon, 26 November 2007 06:11 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

Hi Michel

then what will envoke this procedure whenever the tbl_proc_spend is altered?

NB: the table is altered (during office hours) to capture transactions


hence i ask - then what will envoke this procedure whenever the tbl_proc_spend is altered? five rows has been deleted or added or updated

rebeccah
Re: CREATE A TRIGGER [message #283222 is a reply to message #283195] Mon, 26 November 2007 06:47 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you have to use the procedure when you want to execute a DML on the table.
If you want a trigger, then just add it to check no DML is used out of your procedures.

This is the only way to be sure you don't have data corruption (total that is not the sum of values).
This is the price you have to pay to denormalize your data.

Another way is to create a materialed view (with refresh on commit) to keep the total in sync.

Regards
Michel
Previous Topic: cursor with parameter
Next Topic: Query on two tables
Goto Forum:
  


Current Time: Thu Dec 08 08:37:51 CST 2016

Total time taken to generate the page: 0.14536 seconds