Circled processing [message #409214] |
Sat, 20 June 2009 10:03  |
alammas
Messages: 46 Registered: July 2008
|
Member |
|
|
Sir,
1-I have created base table (BaseTable_tab)
2-Second Materialized view(Mview) based on Base Table.
3-A trigger fires after update and insert on based table with declaring cursor to select from Mview the same record wich insert before trigger fires.
fetched data transfer to a procedure
4-After Process results insert into basetable
Please Comment on:
1-Is that write approach to insert into triggering table
2-What is correct time when oracle update Materialized view (Is that between permnant change on database and trigger Or not)
|
|
|
|
Re: Circled processing [message #409342 is a reply to message #409214] |
Mon, 22 June 2009 03:57   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Interesting solution.
If thisQuote: | 3-A trigger fires after update and insert on based table with declaring cursor to select from Mview the same record wich insert before trigger fires.
| is the reason that you're doing this, then I suspect that you've over complicated things.
In a row level trigger, you can reference the values before the change was made by refering to :OLD.<column_name>, and the values after the update as :NEW.<column_name>
Depending on defined the Mview, it will either refresh on a fixed schedule, or when you tell it to, or when a change is committed to one of the base tables.
Hopefully you've got the third type - if you post the SQL used to create it then we can let you know for certain.
|
|
|
Re: Circled processing [message #411539 is a reply to message #409214] |
Sun, 05 July 2009 00:41   |
alammas
Messages: 46 Registered: July 2008
|
Member |
|
|
Yes i can use:OLD.<column_name>, and the values after the update as :NEW.<column_name>
But just for the sake of knowledge i am just committed.
In fact i still fail to design optimum code for user right at database level
I am attaching file for reference.
Please help me thanx
|
|
|
Re: Circled processing [message #411650 is a reply to message #411539] |
Mon, 06 July 2009 04:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Your trigger has a bug in it: it's got this in the exception handler:EXCEPTION
WHEN OTHERS THEN
NULL;
2) Your Mview never refreshes - no refresh clause was specified, and the default is 'Refresh Full on Demand'
3) V$<name> is a syntax that Oracle uses to identify it's own system views. Your Mview is neither a view, nor part of the Oracle views - why copy their naming system rather than use one that identifies the objects as part of your application?
4) Your trigger TrUserRight never fires - the clause [CODE]WHEN (NEW.ACT = NULL) /CODE] should be
5) In the trigger, you are not passing any parameters into the procedure - the procedure is assuming that the row with the maximum id is the one that it is meant to be using.This is unlikely to work in a multi user environment.
Consider: Session 1 inserts a row, and goes to lunch without committing, Session 2 inserts a row and commits, gaining the new max(id). Session 1 returns, and commits his change - the procedure will now process a row other than the one his session inserted.
Additionaly, the trigger spends a little time chosing Objectid, userid, username and objectname, which are never used.
6) Quote: | But just for the sake of knowledge
| - not the right reason to pick a design.
In summary - I think you're trying to be too clever. You've come up with the fundamentals of an interesting solution, but your implementation needs a lot of work.
|
|
|
Re: Circled processing [message #411694 is a reply to message #409214] |
Mon, 06 July 2009 06:18   |
alammas
Messages: 46 Registered: July 2008
|
Member |
|
|
5) This is unlikely to work in a multi user environment.
Consider: Session 1 inserts a row, and goes to lunch without committing, Session 2 inserts a row and commits, gaining the new max(id). Session 1 returns, and commits his change - the procedure will now process a row other than the one his session inserted.
Some wrong things much more fruitfull then wright,i have to spend much more time to over come , if i do myself.
Mview that refresh on commit to base table and
i realy try to find that if,
1-trigger fires immediate after DML statement
2-Mview refresh on commit to database
trigger execute before (2) or else
Thank you Sir
|
|
|
Re: Circled processing [message #411698 is a reply to message #411694] |
Mon, 06 July 2009 06:37  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
A materialized view that is set to Refresh on Commit wil (as the name says) refresh every time changes are committed to a table.
The trigger will fire before the Mview refreshes.
|
|
|