Home » SQL & PL/SQL » SQL & PL/SQL » update the same table in trigger
update the same table in trigger [message #324998] Wed, 04 June 2008 14:28 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I am trying to create trigger on a table, when any program will try to insert or update any column, it will get fire and update the column of the same row with some value cross refrencing from other table.

Let's say i have one table .

create table tab1
( empno number,
  active_flag  
)


another table tab2 containing this structure

create table tab2
( emp char2(12),
  empno number,
  date_modified,
  flag_indicator
)


Now the reqirement is to create trigger on tab2, so whenever a new record get inserted or update in tab2, it will update the flag_indicator column with the value of active_flag of tab1 table by crossing refrencing the empno.
Is it a good idea to update the same table, which is having the triiger on it.
Any help would be appreciated.

Appreciate your valuable time in advance.

[Updated on: Wed, 04 June 2008 14:40] by Moderator

Report message to a moderator

Re: update the same table in trigger [message #325000 is a reply to message #324998] Wed, 04 June 2008 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is better to insert or update through a procedure if you have complex things to do and not in a trigger.

Regards
Michel

[Updated on: Thu, 05 June 2008 00:33]

Report message to a moderator

Re: update the same table in trigger [message #325002 is a reply to message #324998] Wed, 04 June 2008 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Any help would be appreciated.
Why doesn't the procedure doing the DML provide all the necessary & correct values?
Why is this trigger even necessary?
Re: update the same table in trigger [message #325004 is a reply to message #325002] Wed, 04 June 2008 14:57 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks ana for you valuable time.

Quick question, can we fire that procedure every time the table column get updated or inserted and doing the operation.
If it is, can you give me some sample script.

Thanks alot
Re: update the same table in trigger [message #325005 is a reply to message #325004] Wed, 04 June 2008 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
shoaib123 wrote on Wed, 04 June 2008 12:57
Thanks ana for you valuable time.

Quick question, can we fire that procedure every time the table column get updated or inserted and doing the operation.
If it is, can you give me some sample script.

Thanks alot

Is it not the case that some existing procedure is what is doing the UPDATE or INSERT?
I know for a fact that INSERT or UPDATE do not automagically appear from NOTHING to change tables.
Re: update the same table in trigger [message #325008 is a reply to message #324998] Wed, 04 June 2008 15:10 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
shoaib123 wrote on Wed, 04 June 2008 15:28

create table tab1
( empno number,
  active_flag  
)


another table tab2 containing this structure

create table tab2
( emp char2(12),
  empno number,
  date_modified,
  flag_indicator
)





You do know that these CREATE TABLE statements are not valid in Oracle, right?
Re: update the same table in trigger [message #325009 is a reply to message #325005] Wed, 04 June 2008 15:13 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

i cannot change the existing procedure for some ridiculous reasons.

So, the requirement is to build trigger, so whenever the new record inserted or updated in tab2, it will update flag_indicator column with active_flag by cross refrencing the empno of tab1.

Any sample structure would be appreciate.

Once again ana appreciate your valuable time.
Re: update the same table in trigger [message #325010 is a reply to message #325008] Wed, 04 June 2008 15:14 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Yep Joy. i just wrote those.. just to set an example.
But at the end, You are right.

[Updated on: Wed, 04 June 2008 15:14]

Report message to a moderator

Re: update the same table in trigger [message #325013 is a reply to message #325009] Wed, 04 June 2008 15:19 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Just select from table1 and set the :new value to it.
Re: update the same table in trigger [message #325099 is a reply to message #325009] Thu, 05 June 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i cannot change the existing procedure for some ridiculous reasons.

It is not ridiculous.
Do your trigger and I show you examples when it will not work.

Regards
Michel
Re: update the same table in trigger [message #325301 is a reply to message #325099] Thu, 05 June 2008 11:11 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

here is trigger i created and it is calling procedure but pops up an error.

create table TEMP
(
  FINC_TRAN_ID   NUMBER,
  TRAN_DATE      DATE,
  FLAG_INDICATOR VARCHAR2(12),
  STATE          VARCHAR2(12)
)

create or replace procedure at_temp 
is
begin
delete temp
where finc_tran_id = 2;
end;

create or replace trigger at_temp
after insert or update on temp
for each row
begin
at_temp;
end;

Then when i insert some records it pops up this error

ERROR at line 1:
ORA-04091: table TEMP is mutating, trigger/function may not see it
ORA-06512: at "AT_TEMP", line 4
ORA-06512: at "AT_TEMP", line 5
ORA-04088: error during execution of trigger 'AT_TEMP'




Re: update the same table in trigger [message #325302 is a reply to message #324998] Thu, 05 June 2008 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that both GOOGLE & the search function on this forum are broken for you & you can't not find a solution to your FAQ.

Please be patient while repairs are underway.

A more complete response might appear any time now.
Re: update the same table in trigger [message #325309 is a reply to message #325302] Thu, 05 June 2008 12:23 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I Found the problem.. i appreciate your comments and your valuable time.

Time is game Laughing ..
Re: update the same table in trigger [message #325312 is a reply to message #325309] Thu, 05 June 2008 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
shoaib123 wrote on Thu, 05 June 2008 10:23
I Found the problem.. i appreciate your comments and your valuable time.

Time is game Laughing ..


I understand.
You would rather waste other people's time rather than your own.
But this way hopefully, you actually learned something new.
Re: update the same table in trigger [message #325509 is a reply to message #325312] Fri, 06 June 2008 08:02 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Please somebbdy can have a look at my code and let me know if there any syntax error in the IF condition. I test this code with several values, and each time it print the first dbms statement. It is not checking the second if condition and so on.


declare
  v_cnt varchar2(12);
begin
 select substr(flag_indicator,1,1)
      into v_cnt
      from temp1
     where finc_tran_id = 14578;      
  if (v_cnt != 'R') or (v_cnt is null) or
     (v_cnt != 'N')) then
     dbms_output.put_line('loop1');
    --:new.flag_indicator := v_cnt;
  else if (v_cnt = 'R') or (v_cnt = 'N'))
   then 
   dbms_output.put_line('loop2);
   end if; 
  end if;
exception
when NO_DATA_fOUND then  
 dbms_output.put_line('loop3);
end;

I appreciate you valuable time.

[Updated on: Fri, 06 June 2008 08:02]

Report message to a moderator

Re: update the same table in trigger [message #325511 is a reply to message #325509] Fri, 06 June 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't have your temp1 table, so we can't reproduce your error.
In addition, it is better to use SQL*Plus and copy and paste your execution including line numbers and error.

Regards
Michel

[Updated on: Fri, 06 June 2008 08:06]

Report message to a moderator

Re: update the same table in trigger [message #325512 is a reply to message #325509] Fri, 06 June 2008 08:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shoaib123 wrote on Fri, 06 June 2008 15:02

if (v_cnt != 'R') or (v_cnt is null) or
(v_cnt != 'N')) then

Can you give me an example for which this is NOT true?

Think hard before answering 'R' or 'N'...



By the way, this is NOT a syntax error. Syntax errors are logged at compile-time.

[Updated on: Fri, 06 June 2008 08:08]

Report message to a moderator

Re: update the same table in trigger [message #325513 is a reply to message #325512] Fri, 06 June 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually if I count the parenthesis, there is a mismatch in:
Quote:
if (v_cnt != 'R') or (v_cnt is null) or
(v_cnt != 'N')) then


Regards
Michel
Re: update the same table in trigger [message #325514 is a reply to message #324998] Fri, 06 June 2008 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>if (v_cnt != 'R') or (v_cnt is null) or (v_cnt != 'N')) then
statement above is ALWAYS true
Re: update the same table in trigger [message #325517 is a reply to message #325511] Fri, 06 June 2008 08:34 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I do oplolgize, i was not able to clear question.

The is not that i am getting error. The problem is this, every time i am execting this procedure with the diefferent value, it is always executes the first dbms_oupt statement.

let's say..
Let's say i don't have the value which i am using in my cursor, it should print the third dbma_out..which is loop3. Instead of that, it is again printing the 1st dbms_out, which is loop1.

Ok then,

if if i have the value of finc_tan_id which i am using in the cursor, again it print the first dbms_output

Apprecited everbody's time.
Re: update the same table in trigger [message #325518 is a reply to message #324998] Fri, 06 June 2008 08:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Why do you not construct the truth table for that condition? Is it so hard to paint something like this?
V_CNT  != R    IS NULL  != N    (OR)
------------------------------------
NULL   NULL    TRUE     NULL    TRUE
R      FALSE   FALSE    TRUE    TRUE
N      TRUE    FALSE    FALSE   TRUE
X      TRUE    FALSE    TRUE    TRUE

Now, write the result you expect and find the combinations of comparisons leading to it.
Re: update the same table in trigger [message #325520 is a reply to message #325517] Fri, 06 June 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 06 June 2008 15:06
We don't have your temp1 table, so we can't reproduce your error.
In addition, it is better to use SQL*Plus and copy and paste your execution including line numbers and error.

Regards
Michel

Re: update the same table in trigger [message #325529 is a reply to message #325518] Fri, 06 June 2008 09:00 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Again, i am not getting error message. It is just every time i test using different the finc_tran_id, end up only getting the first dbms_out statement.

The table looks like this.

Finc_tran_id              Flag_indicator
1408                       null
14578                      R
145781                     N
145782                     P
Re: update the same table in trigger [message #325545 is a reply to message #325529] Fri, 06 June 2008 09:52 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks everybody..

The problems is solved, The answer is

anacedent   
 
>if (v_cnt != 'R') or (v_cnt is null) or (v_cnt != 'N')) then
statement above is ALWAYS true
 


That was the only problem, that's why i was getting same dbms_output message, every time i execute the procedure.

Once again thanks to everybody..

[Updated on: Fri, 06 June 2008 09:53]

Report message to a moderator

Re: update the same table in trigger [message #325566 is a reply to message #325545] Fri, 06 June 2008 10:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now if both you and anacedent would have read every reply you gotten...
Re: update the same table in trigger [message #325568 is a reply to message #325566] Fri, 06 June 2008 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Frank wrote on Fri, 06 June 2008 08:22
Now if both you and anacedent would have read every reply you gotten...

Frank,
I realize that you effectively posted same response, but some folks are clue resistant & others are clue repellent and require additional assistance.
Re: update the same table in trigger [message #325578 is a reply to message #325566] Fri, 06 June 2008 10:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Frank wrote on Fri, 06 June 2008 17:22
Now if both you and anacedent would have read every reply you gotten...

If you would have read every reply, you surely would not omit me Wink
Re: update the same table in trigger [message #325603 is a reply to message #325578] Fri, 06 June 2008 12:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
grmbl..

ok, got the point, no more mr Wiseguy for me Wink
Previous Topic: PAssing table and column names as parameters
Next Topic: Getting the exact elapsed time of a SQL
Goto Forum:
  


Current Time: Fri Dec 09 00:04:23 CST 2016

Total time taken to generate the page: 0.19238 seconds