Home » SQL & PL/SQL » SQL & PL/SQL » Triggers / Cursors with outer join (merged)
Triggers / Cursors with outer join (merged) [message #201451] Sat, 04 November 2006 07:10 Go to next message
ajomara
Messages: 3
Registered: November 2006
Junior Member
Hi, how to get the falsy record in outer join by using
cursors.
my question is with out a record how the cursor fetches?
Re: cursors with outer join [message #201473 is a reply to message #201451] Sat, 04 November 2006 14:18 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

You misunderstand. Cursors has no special relationship to outerjoins.

When outerjoining, the 'missing' record from say table b and there. it is just blanks (nulls).

select * from a,b where a.id = b.id(+)

If You are looking for the records where the outerjoin kicked in, You could

a) just add AND b.id IS NULL
b) rewrite using MINUS, NOT EXISTS etc.

Br
Kim
triggers [message #201862 is a reply to message #201451] Tue, 07 November 2006 02:35 Go to previous messageGo to next message
ajomara
Messages: 3
Registered: November 2006
Junior Member
hi,

You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a
SELECT statement on the EMP table to ensure that the new salary value falls within the
minimum and maximum salary for a given job title. What happens when you try to update a
salary value in the EMP table?
Re: triggers [message #201866 is a reply to message #201473] Tue, 07 November 2006 02:42 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
I this a quiz?
What is the prize?

I do not have a trigger on such a table.
And I really do not understand Your question.

My guess is though that it will fail.
But I really do not understand why You ask a question like this.

Re: triggers [message #201868 is a reply to message #201866] Tue, 07 November 2006 02:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You know "kim" what is better solution for above question.
"try to first urself" and see what happens.

Wink

and OP already post his query here.
http://www.orafaq.com/forum/t/71964/93410/

have fun
Re: triggers [message #201869 is a reply to message #201862] Tue, 07 November 2006 02:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Homework? Quiz?

MHE
Re: triggers [message #201870 is a reply to message #201862] Tue, 07 November 2006 02:49 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Hi,

Use Pragma anonmous_transaction to select on EMP table to prevent mutation error.

e.g.
funtion check_emp_sp (curr_sal in number) return number is
pragma anonmous_transaction;
begin
/*ur condition to check if the salary is between min & max as per your requirement.*/
end;
and call this function with in your trigger for validation of new value.

Hope it will work for u.

Sandy
Re: triggers [message #201871 is a reply to message #201870] Tue, 07 November 2006 02:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sandeepk7 wrote on Tue, 07 November 2006 09:49
ur condition
What's a "ur condition"?

MHE
Re: triggers [message #201873 is a reply to message #201862] Tue, 07 November 2006 02:55 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

as how he wants to check the validation. might he needs to find min & max according to dept or job title, what ever.

Sandy
Re: triggers [message #201894 is a reply to message #201870] Tue, 07 November 2006 04:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sandeepk7 wrote on Tue, 07 November 2006 09:49
Hi,

Use Pragma anonmous_transaction to select on EMP table to prevent mutation error.

e.g.
funtion check_emp_sp (curr_sal in number) return number is
pragma anonmous_transaction;
begin
/*ur condition to check if the salary is between min & max as per your requirement.*/
end;
and call this function with in your trigger for validation of new value.

Hope it will work for u.

Sandy


Better advice: review your logic. If you need a select on the triggering table in a trigger, 99.999% your logic has a flaw.
Re: triggers [message #201913 is a reply to message #201894] Tue, 07 November 2006 06:06 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Quote:
Better advice: review your logic. If you need a select on the triggering table in a trigger, 99.999% your logic has a flaw


Can u please explain what is worng with the logic. What i suppose when using pragma autonomous_transaction, that function will be opened in a different session and will select the same table data in that session so it shouldn't cause any problem. Please let me know if something wrong.....

sandy

[Updated on: Tue, 07 November 2006 06:30]

Report message to a moderator

Previous Topic: Picking one language
Next Topic: ORA-01722 in Bulk Bind
Goto Forum:
  


Current Time: Sat Dec 10 18:39:37 CST 2016

Total time taken to generate the page: 0.12956 seconds