I have a newbie question as am a newbie in this domain:
There is one table : table1 which looks like:
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...
yes the data integrity is the reason.
That still does not explain WHAT the data integrity rule is. One needs to understand the WHAT and WHY, before coming up with the HOW.
for one specific row it has to be fixed value, however update modifies all rows
* row can be identified as :
.... where col1 = 'row2'....
Not good enough. As that means the trigger needs to have the text 'row2' hardcoded as a check. And what happens if row200 needs to be treated the same fashion when the rule in the trigger only works for row2?
And why only for row2? If row2 is so special and unique, and treated differently from each and every other row in that table, wtf is it doing in that table? Surely that row (despite similar attributes) is a different type of entity than what the other rows in that table represents - which means it should be in a different table? Or the existing entity's design is flawed.
As I said - we need to understand the problem.All you have offered is a broken solution (mutating trigger/hardcoded column values in trigger) - how do you expect this forum to fix that broken solution if the problem that the solution need to address is unknown?
thanks Billy for your answer and suggestions for thinking the solution on large scale.
But, somehow you might understand that when we work in huge enterprise system, you may not have all the privileges you need to implement the perfect solution as we can get in school project where the whole system is under one man control. Moreover, it might go under long chain of approvals, review etc. from geographically spread team which might only delay the quick fix.
Well, to your query on what, why how. I can say in short :
* that table is getting updated by a third party vendor system(not in our control) so we can't touch that code(<--probably the best fix)
* wtf this row doing in this table : this is very old system designed with some specific purpose, down the line biz requirements changed for regulatory compliance etc. and only that row needs to keep a FIXED value, while other rows keep on getting modified as usual from external system.
* you are right, it should have been in a different table, but do you think it as quick fix for the reasons I mentioned above.
** So, what I am thinking is : just write a trigger --> to get its old value back after every update on this table.
well I appreciate you answer though