update records- trigger [message #606336] |
Wed, 22 January 2014 08:17 |
|
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 #606363 is a reply to message #606338] |
Wed, 22 January 2014 21:04 |
|
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 #606390 is a reply to message #606364] |
Thu, 23 January 2014 04:22 |
|
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 |
John Watson
Messages: 8938 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 |
|
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 |
|
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 |
|
Littlefoot
Messages: 21811 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 |
|
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
|
|
|
|
|