| Update trigger (merged 2) [message #622244] |
Sat, 23 August 2014 09:15  |
kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania
|
Senior Member |
|
|
Hello friends
I have two tables, members_Lz and members_loan.Both have status and reg_numbers fields .I first feed data into members_loan then later into members_lz.
I want when members_lz status to change into complete, members_loan status to change into 'processed' from Pending.
I wrote this trigger and it compiled successful with no errors ,but it doesn't change the status of the members_loan.Please ,if you can help.
Thank you.
CREATE OR REPLACE TRIGGER mikopo
AFTER UPDATE OF REQUEST_NUMBER
ON members_lZ
BEGIN
UPDATE members_loan
SET loan_status ='PROCESSED'
WHERE request_number in (select REQUEST_NUMBER from members_lZ);
END;
|
|
|
|
|
|
|
|
| Re: Update trigger [message #622251 is a reply to message #622246] |
Sat, 23 August 2014 11:32  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The update you wrote will update every row in members_loan that has a matching record in members_lZ. Which doesn't appear to be what you want but should update some records.
Sounds like you want a row level trigger rather than the statement level you currently have.
You want it update the row in members_loan that corresponds to the row in members_lz that's currently being updated.
And the update should only fire if the status of members_lz hs changed in the way you want.
If you're not sure how to refer to the values of the current row in a trigger then I suggest you spend a while reading the chapter on coding triggers in documentation
|
|
|
|
| Re: Update trigger [message #622252 is a reply to message #622246] |
Sat, 23 August 2014 11:36 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@OP, is it done from an application? How is the first table being updated? What I mean is, if you have a procedure currently updating the first table, then include another update statament to update the second table based on the status of first table's respective column.
It is also important to know that how these tables are related to each other. I would personally avoid a trigger for such requirement as it is always a side effect of an action. If you could provide a test case, including the two tables, some sample data and the update statement that you fire on the first table.
Regards,
Lalit
|
|
|
|
| Re: Update trigger [message #623136 is a reply to message #622251] |
Fri, 05 September 2014 01:22 |
kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania
|
Senior Member |
|
|
Hello Cookiemonster
Thank you for your help.I managed it.
I have four final problems though.Please if you can help me.
How do you create a log file which can track all changes made by the users?
How do you assign different menu items to some users ?
I'm using oracle 11G R2 on window 7 ultimate.My developer is 6i. MY question here is ,every time I run the reports a log on screen pop up.How can I bypass it?
lastly Can Developer six be web based. E.g, I host the system I have developed somewhere else and the users access it from any location global.Thank you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
