Home » Developer & Programmer » Forms » update records- trigger (oracle 10g forms and reports 9i)
update records- trigger [message #606336] Wed, 22 January 2014 08:17 Go to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

hi,

i need help in using trigger to update a column in one table when a record is inserted in another table.

I am not sure if i am making it clear. will try to explain in detail

There is a table AUTHORITY where pensionery benefits are authorized for a particular authorisation no.

For having made payment , a voucher will be sent and this is posted in another table voucher.

What i require is when this voucher is posted in VOUCHER table and saved, there should be a "Y" UPDATED in AUTHORITY table in column name 'AUTH_PAID'

the link between these two tables in the AUTHORITY.AUTH_PK=VOUCHER.VCH_AUTH_PK

which trigger should i use and in which table it should be used either in VOUCHER TABLE OR IN AUTHORITY TABLE. Alternatively should the trigger be in database.

since i do not have much knowledge on these triggers, a coding for this will be of great help.
thanks in advance
Re: update records- trigger [message #606338 is a reply to message #606336] Wed, 22 January 2014 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So when a record is inserted in voucher you want the system to update the matching authority record?
Re: update records- trigger [message #606363 is a reply to message #606338] Wed, 22 January 2014 21:04 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

yes. just to make sure all authority authorised are paid to the beneficiary. So In AUTHORITY table i have added a column AUTH_PAID. In this column after a record is inserted in VOUCHER table the system should update the AUTH_PAID column in AUTHORITY table as 'Y'
Re: update records- trigger [message #606364 is a reply to message #606363] Thu, 23 January 2014 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No doubt, a database trigger on the VOUCHER table is capable of doing that job.

A form trigger can do that as well (for example, PRE-UPDATE or PRE-INSERT).
Re: update records- trigger [message #606390 is a reply to message #606364] Thu, 23 January 2014 04:22 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

thanks you sir.

is it possible to give me a sample code. I am not sure what should be the If condition before updating Authority table. like it should be after committing a record. sorry i am clueless.
Re: update records- trigger [message #606392 is a reply to message #606390] Thu, 23 January 2014 04:28 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have already studied the syntax and examples of triggers in the docs? (The correct answer is "ÿes").
Probably what you need is an AFTER INSERT trigger the executes FOR EACH ROW. A trigger executes as part of the DML statement, so it does it's work before the COMMIT and this work will be committed as part of the calling transaction.
Re: update records- trigger [message #606400 is a reply to message #606392] Thu, 23 January 2014 05:50 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

yes sir, i studied it and i used after insert trigger only. i think i couldnt get it right. i will try it again looking at the docs and if i still am not able to get it i will post my after insert trigger for help

thanks a lot
Re: update records- trigger [message #606436 is a reply to message #606400] Thu, 23 January 2014 21:34 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

sir

i did try this code as After insert trigger. but i am getting compilation error. could you please suggest correction to this code.
CREATE OR REPLACE TRIGGER AUTH_VCH_POST
    AFTER INSERT ON T_VOUCHER_HDR
    FOR EACH ROW
BEGIN
    UPDATE M_PEN_AUTHORITY
    SET AUTH_PAID='Y'
    WHERE AUTH_PK IN (SELECT VCH_AUTH_PK FROM T_VOUCHER_HDR,M_PEN_AUTHORITY
                      WHERE VCH_PPO_TYPE='762'
                        AND AUTH_PK=:VCH_AUTH_PK
                        AND AUTH_PNSR_PK=:AUTH_PNSR_PK 
                     );
END AUTH_VCH_POST;


when i tried this update command even when i specify the values for the parameters VCH_AUTH_PK and AUTH_PNSR_PK
it was getting updated for all the records. The update command also is giving some problem.

i would be thankful for the help shown


[EDITED by LF: fixed [code] tags]

[Updated on: Fri, 24 January 2014 00:02] by Moderator

Report message to a moderator

Re: update records- trigger [message #606441 is a reply to message #606436] Fri, 24 January 2014 00:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't reference columns by "colon + column name" (e.g. ":VCH_AUTH_PK"). These would be ":NEW.VCH_AUTH_PK" or ":OLD.VCH_AUTH_PK", depending on what you are doing.

UPDATE statement updated all columns in the M_PEN_AUTHORITY table because UPDATE lacks in WHERE condition which would further restrict records that should be updated.

Although it is probably clear which column belongs to which table, for the rest of us it is a mystery. Take it as a best practice and always precede column names with table aliases. That makes your code easier to read and maintain. Believe me, much sooner than you expect, you'll forget which table which column belongs to.

[Updated on: Fri, 24 January 2014 00:03]

Report message to a moderator

Re: update records- trigger [message #606454 is a reply to message #606336] Fri, 24 January 2014 02:24 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

sir,
CREATE OR REPLACE TRIGGER AUTH_VCH_POST
    AFTER INSERT ON T_VOUCHER_HDR
    FOR EACH ROW
BEGIN
    UPDATE M_PEN_AUTHORITY
    SET AUTH_PAID='Y'
    WHERE AUTH_PK IN (SELECT B.VCH_AUTH_PK FROM T_VOUCHER_HDR A,M_PEN_AUTHORITY B
                      WHERE B.VCH_PPO_TYPE='762'
                        AND A.AUTH_PK=:NEW.VCH_AUTH_PK);                  
END AUTH_VCH_POST;


I removed the colon and replaced with NEW. but now it gives a mutating trigger / function error and the records are also not getting inserted in T_VOUCHER_HDR TABLE.

I don't know where i have gone wrong in writing the trigger and update command. As for the Update command lacking where condition instead of giving the values in parameter if i hard code it then it updates only for that record.


[EDITED by LF: fixed [code] tags]

[Updated on: Fri, 24 January 2014 02:42] by Moderator

Report message to a moderator

Re: update records- trigger [message #606455 is a reply to message #606454] Fri, 24 January 2014 02:41 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the purpose of a subquery which selects from two tables which aren't joined? Are you sure that this is the right way to do that? How about
update m_pen_authority m set
  m.auth_paid = 'Y'
  where m.auth_pk = :new.vch_auth_pk;


Besides, you can't select from T_VOUCHER_HDR at the same time you are inserting/updateing it (that's why you got mutating table error).
Re: update records- trigger [message #606469 is a reply to message #606455] Fri, 24 January 2014 06:24 Go to previous message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

thanks a lot sir. i got it . will have to check for more records and see if its happening correctly.

thanks a lot once again
Previous Topic: Text field goes to the before field
Next Topic: use of $$DBDATETIME$$ at other than initial value
Goto Forum:
  


Current Time: Fri Mar 29 00:32:25 CDT 2024