asked
Update entire column after update or insert
There is a table with two columns. Table: NewID Int TrueFalse bit The bit column is either a 1 or 0. I want to update the bit column for every row anytime a new row is added or updated. So if a new row is inserted that is a '0' then every row will update with a '0' in the TrueFalse column. If a '1' is updated in the TrueFalse column, then every row will update to a '1' in the TrueFalse column. My first inclinations have been hackneyed and quite poor (trigger, cursor, cursor inside a trigger, etc.) so any advice or ideas are appreciated.
update the complete records after each insert/update.
INSERT INTO Table (NewID, TrueFalse)
VALUES(@ID,@TrueFalse)
UPDATE Table
SET TrueFalse = @TrueFalse
Sorry for beeing blunt, but I got scared of the design that will eventually blow up in someones face when the amount of data has grown to a certain point where no hardware in the world could save the system.
@Hakan - No need to be sorry IMO. Your explanation left no uncertainty for the OP that the design *must* be changed, hence the +1 on your answer from me. I only wanted Cyborg to know his answer is valid from the T-SQL side, but does not indicate the need to fix the broken design.
That is a terrible design! What do you think will happen if you have a million records an are trying to insert another million? The first million record gets updated a million times and all the new record inserted will be updated for each records left! What business rule are you trying to solve with this design? Give us some more information and we may try to help you solve this in an efficient way.
It is a backup parameters table with less than 30 rows(each row is a database and its backup drive, parameters, etc.). We have to keep more than two weeks of full backups on disk and will keep one week of fulls on one disk and another week of fulls on another disk depending on the week. I was asked by my boss to write the code that would update the column on an update/insert. I am assuming this is more of an academic/education exercise since I am not as experienced. I appreciate the warnings and understand (even with my limited experience) that this is poor overall design, but it is what my task is. Thanks.
