Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for update on each row (Oracle 9i, Windows Server 2000)
Trigger for update on each row [message #430299] Mon, 09 November 2009 23:57 Go to next message
namb
Messages: 35
Registered: September 2009
Member
Dear Friends,

I am trying to create a trigger during update of each row of my temporary table musterdata so that this trigger gets fired when ever the row of my musterdata table gets updated, it should automatically update those values in my parent table

The temp table structure is

Desc musterdata;

EID VARCHAR2(10)
MUSTERDATE DATE
IN_TIME VARCHAR2(5)
OUT_TIME VARCHAR2(5)
OT VARCHAR2(5)
VC_SHIFT_CODE VARCHAR2(2)


My parent table has the structure:

VC_COMP_CODE NOT NULL VARCHAR2(2)
VC_EMP_CODE NOT NULL VARCHAR2(Cool
VC_CATG_CODE VARCHAR2(4)
VC_UNIT VARCHAR2(4)
VC_BLOCK VARCHAR2(4)
VC_DEPT_CODE VARCHAR2(4)
DT_ATTEN NOT NULL DATE
VC_FIRST_LEAVE VARCHAR2(4)
VC_SECOND_LEAVE VARCHAR2(4)
NUM_TIME_IN VARCHAR2(5) NUM_TIME_OUT VARCHAR2(5) VC_SHIFT_CODE VARCHAR2(2)
EXTRA_HRS_PH NUMBER(4,2)
EXTRA_HRS_ANS NUMBER(4,2)
EXTRA_HRS_WOFF NUMBER(4,2)
TOTAL_HRS_WORKED NUMBER(5,2)
EXTRA_HRS_NH NUMBER(4,2)
CH_OT_APPROVED CHAR(1)
VC_DEFAULT_COMP VARCHAR2(2)
CH_STAT_FLAG CHAR(2)
CH_STAT_UP_FLAG CHAR(2)
DT_MOD_DATE DATE
VC_AUTH_CODE VARCHAR2(2)
VC_FIELD1 VARCHAR2(30)
VC_FIELD2 VARCHAR2(30)
VC_FIELD3 VARCHAR2(30)
VC_FIELD4 VARCHAR2(30)
DT_FIELD1 DATE
DT_FIELD2 DATE
NU_FIELD1 NUMBER(20,3)
NU_FIELD2 NUMBER(20,3)
VC_OT_SHIFT VARCHAR2(2)
OVT_AMT NUMBER(5,2)


I want to map fields as follows

EID-->VC_EMP_CODE
MUSTERDATE--->DT_ATTEN
IN_TIME-->NUM_TIME_IN
OUT_TIME-->NUM_TIME_OUT
OT-->OVT_AMT
VC_SHIFT_CODE-->VC_SHIFT_CODE


Please help
Re: Trigger for update on each row [message #430300 is a reply to message #430299] Tue, 10 November 2009 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
>Please help
We don't do homework here.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Re: Trigger for update on each row [message #430314 is a reply to message #430299] Tue, 10 November 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must not do these kind of things with a trigger.
Do it in clear in a procedure.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Trigger for update on each row [message #430320 is a reply to message #430314] Tue, 10 November 2009 01:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 10 November 2009 07:34
You must not do these kind of things with a trigger.
Do it in clear in a procedure.

Even better: don't do it at all. Design your datamodel in such a way that you don't store duplicate data.
Previous Topic: create custom function in oracle (merged)
Next Topic: Question on error handling
Goto Forum:
  


Current Time: Wed Dec 07 08:41:21 CST 2016

Total time taken to generate the page: 0.15372 seconds