This discussion is archived
12 Replies Latest reply: Jul 26, 2013 9:59 AM by John Spencer RSS

update trigger on update in same table

whitecat Newbie
Currently Being Moderated

Hi guys!

 

I have a newbie question as am a newbie in this domain:

 

There is one table : table1 which looks like:

Col1

Col2

row1

     1

row2

     2

row3

     3

 

I need to write a simple trigger to achieve:


* whenever there is change in row2 for  col2 value ('2') to some other value (e.g. '5') , i need to reset the value back to its original value('2').


I tried writing simple trigger for this but since both operations(original update statement and my trigger update statement) are on same table it throws some error like... cant handle more than 50 recursions or invalid trigger etc.


* also pleas let me know, if there is any other better way around rather than using trigger. Is there any way to monitor at schema level etc. that can track that there has been some data update and then immediately run my update trigger on that...


* 1 idea I can think of is : create another table with same data update it as needed and copy back to original table, but it looks more of work + authority problem too...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points