Home » SQL & PL/SQL » SQL & PL/SQL » Circled processing (Oracle 10g,WinXP)
Circled processing [message #409214] Sat, 20 June 2009 10:03 Go to next message
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 #409216 is a reply to message #409214] Sat, 20 June 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
MViews are updated by Oracle you don't need to do anything.

Regards
Michel
Re: Circled processing [message #409342 is a reply to message #409214] Mon, 22 June 2009 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting solution.
If this
Quote:
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
WHEN (NEW.ACT IS NULL)


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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: sub query logic
Next Topic: Monitoring transactions
Goto Forum:
  


Current Time: Thu Feb 13 09:55:25 CST 2025