How To Create Non Effect Update
-
I have just created a trigger in a database but I want get it to run now on every row in the table. I figured the best way to do this would be a table wide update that does nothing. But I can't work out how to create such a thing. I have tried
UPDATE table
SET column = columnBut no luck!
-
I have just created a trigger in a database but I want get it to run now on every row in the table. I figured the best way to do this would be a table wide update that does nothing. But I can't work out how to create such a thing. I have tried
UPDATE table
SET column = columnBut no luck!
-
I have just created a trigger in a database but I want get it to run now on every row in the table. I figured the best way to do this would be a table wide update that does nothing. But I can't work out how to create such a thing. I have tried
UPDATE table
SET column = columnBut no luck!
If this is a one time thing, why don't you take the basic code from your trigger and wrap it in some TSQL to call it. You may have to use a cursor to loop through your set of records to get it to work. I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required.
-
I suspect your trigger isn't doing what you expected as your update will do the update as you wanted.
Bob Ashfield Consultants Ltd
My bad. I tried a non-effect update query on my database where col1=col1 and according to the client statistics captured in SQL Server Management Studio, the rows affected were 43,657 in my database. I would then believe that the trigger logic would have been invoked.
-
If this is a one time thing, why don't you take the basic code from your trigger and wrap it in some TSQL to call it. You may have to use a cursor to loop through your set of records to get it to work. I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required.
Based on a test I just performed, I beleive the trigger should have fired. (See earlier post ...) Go back and revisit your trigger logic.
-
If this is a one time thing, why don't you take the basic code from your trigger and wrap it in some TSQL to call it. You may have to use a cursor to loop through your set of records to get it to work. I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required.
I'm pretty sure it will, I've used the same technique in the past. A quick way to check, start a transaction, do the update and without a commit or rollback, see (on another connection) if you can do anything with the table. If the optimiser was smart enough to know not to do an update the table will not be locked. My best guess (without seeing the code) is that the trigger is not working correctly - maybe expecting single records in the inserted/deleted tables?
Bob Ashfield Consultants Ltd
-
If this is a one time thing, why don't you take the basic code from your trigger and wrap it in some TSQL to call it. You may have to use a cursor to loop through your set of records to get it to work. I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required.
David Mujica wrote:
I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required
That would be a serious bug. Optimizer cannot make decisions which would compromise or change the logic. If it would eliminate row updates based on the fact that no data is changed in the statement itself, it also would have to understand trigger logic. Also what if the table contains a timestamp column, foreign keys etc. Typically optimizers ensure that the logic is not changed and if there is even a slight chance that the logic may be affected, optimizer revokes such permutations because of the uncertainty.
The need to optimize rises from a bad design.My articles[^]