Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (merged) (Oracle 10g)
Triggers (merged) [message #431104] Sun, 15 November 2009 00:48 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have two tables as follows

1.ot_adj_item

adji_item_code
adji_locn
adji_sys_id
adji_flex_01
adji_rate
adji_value

2.ot_adj_batch

adjb_batch_no
adjb_sys_id
adjb_adji_sys_id references adji_sys_id of ot_adj_item


I am writing one trigger after update on ot_adj_batch which does the following.Pick the batch from batch table and based on item entered in adji_flex_01 it will update the rate in adji_rate.But my trigger as below doesnt do that.Please help

 CREATE OR REPLACE TRIGGER ORION2007.UPD_ADJ_CUT
 BEFORE INSERT OR UPDATE ON ORION2007.OT_ADJ_BATCH FOR EACH ROW
 DECLARE
 CURSOR C1 IS
 SELECT O_DGET_BATCH_RATE('AIC',ADJH_LOCN_CODE,ADJI_FLEX_01,'A','NA',ADJB_BATCH_NO) RATE
 FROM OT_ADJ_BATCH,OT_ADJ_ITEM,OT_ADJ_HEAD
 WHERE ADJH_SYS_ID = ADJI_ADJH_SYS_ID
 AND ADJI_SYS_ID = ADJB_ADJI_SYS_ID
 AND ADJB_SYS_ID = :NEW.ADJB_SYS_ID
 AND ADJH_TXN_CODE = 'S_TRF';
 M_RATE NUMBER;
 BEGIN
  FOR I IN C1
   LOOP
   UPDATE OT_ADJ_ITEM SET ADJI_RATE = I.RATE,ADJI_VAL= (ADJI_QTY_BU/1000)*I.RATE
   WHERE ADJI_SYS_ID = :NEW.ADJB_ADJI_SYS_ID;
   END LOOP;
 END;

[Updated on: Sun, 15 November 2009 00:57] by Moderator

Report message to a moderator

Re: Triggers [message #431106 is a reply to message #431104] Sun, 15 November 2009 00:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
HUH?
What is it doing or not doing?
Re: Triggers (merged) [message #431107 is a reply to message #431104] Sun, 15 November 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have two tables

BEFORE INSERT OR UPDATE ON ORION2007.OT_ADJ_BATCH
FROM OT_ADJ_BATCH,OT_ADJ_ITEM,OT_ADJ_HEAD
UPDATE OT_ADJ_ITEM

There are more tables.

Without speaking of what it should do or not as you don't explain us:
1/ Properly indent your code (and use code tags)
2/ You cannot select a table you are currently modify
3/ Why looping and not using a single SQL statement
4/ Anyway this will never work in multiuser environment unless you lock the table(s)

Regards
Michel
Re: Triggers (merged) [message #431109 is a reply to message #431107] Sun, 15 November 2009 01:35 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Michael,

Please tell me how to add the code tags as i dont know and there is no article on it.Please help me writing this trigger.

Re: Triggers [message #431112 is a reply to message #431106] Sun, 15 November 2009 02:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Please help otherwise dont give sarcastic comments
Re: Triggers (merged) [message #431114 is a reply to message #431109] Sun, 15 November 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please tell me how to add the code tags as i dont know and there is no article on it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Quote:
Please help me writing this trigger.

Please explain each table purpose and what should do the trigger.
But as I said: you can't do it with a trigger unless you previously lock the table(s) before each insert or update.

Regards
Michel
Re: Triggers (merged) [message #431115 is a reply to message #431114] Sun, 15 November 2009 02:44 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear michael ,

The purpose is there are two tables as i mentioned below

1.ot_adj_item
2.ot_adj_batch

the link between two tables is adji_sys_id.ot_adj_item = adjb_adji_sys_id

what i am trying to do is i am entering item a in ot_adj_item and there is one field where i am entering another item b.Item a is new and it needs to copy the rate from item b based on batch in ot_adj_batch.There is a function encoded in the select which brings the rate.
Re: Triggers (merged) [message #431118 is a reply to message #431115] Sun, 15 November 2009 04:55 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The purpose is there are two tables as i mentioned below

Giving the name of the table does NOT give the purpose of these ones and their relation, so unable to know if the trigger does what it should do.
In addition there are NOT 2 tables but more as I already said.

Quote:
But as I said (now for the third time): you can't do it with a trigger unless you previously lock the table(s) before each insert or update.

You can ignore my remark but this is true.

Regards
Michel
Previous Topic: Invalid number exception returned by Store Procedure (merged)
Next Topic: Oracle 9i (merged)
Goto Forum:
  


Current Time: Sat Dec 10 20:35:35 CST 2016

Total time taken to generate the page: 0.08218 seconds