I need to write a trigger on a table that uses some data from other tables. This data is coming from a select statement on a table and is almost always constant. Please let me know if there is a way to cache the query result set in memory and access this data from a trigger.
Thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Can you please eloborate on the part where you mentioned about caching the result set in a temporary table or object. I want a trigger on a table XXX to read data from a table YYY every time it gets triggered. This will prove to be expensive without any caching. Please give your suggestions with some code templates if possible.
Thanks in advance.
Sure, but can you tell me a little more about this select from "YYY"?
1) How many records is it?
2) Is there a where clause?
3) How many columns?
And, for the trigger - does it have to fire for each row? And, is it more likely that there will be multiple records in a triggering event (insert/update/delete) or just one at a time even though there many be multiple users generating triggering events throughout the day.
This would help me to better structure the objects involved.
Good luck!
Here's a website that discusses the 11g feature KICUSek mentioned. I don't know that it's worth upgrading just for this feature, but just so you know...
http://www.oracle-develope
Good luck!
DrSQL,
Please see my comments below.
1) How many records is it? Table YYY is a table of constant codes. It may have 100 recs atmost.
2) Is there a where clause? Trigger may need to read only 1 row from this cached table (YYY)... Yes there will be a where clause.
3) How many columns? Reads 2 columns.
Table YYY is to be used on every time it is triggered; you only want to load it once, not to read table every time trigger is called on the table XXX.
Business Accounts
Answer for Membership
by: DrSQLPosted on 2008-07-17 at 18:26:24ID: 22032037
Well, you can make the table a cached table:
alter table MyTable cache;
Which says that it stays in the small table cache until the buffers absolutley need to be flushed.
But, if you want the RESULTS to be cached, you would need to create another object (probably a table) that could be cached. A package can also maintain data across calls to entry point within it, but that is usually session specific (you write an addition block os pl/sql code that initializes your variables and the first time a procedure from that package is called the block is run and any global variables retain their values). But, if you were to set up a service that ran as a daemon of scheduled job you could use a package that would keep the results in a cursor and then use advanced queue or messaging (which Oracle has built into supplied pl/sql packages) to transmit the data to the trigger.
Another approach is to use an index that contains all of the column, with the most selective columns listed first. If all of the columns that the trigger might use (in any way, not just in a where clause), then there's no need for the table to be read.
Good luck!