January 25, 2011 at 8:36 am
Thanks a lot, it makes sense.
January 25, 2011 at 8:57 am
This is not a trigger to require a WHERE clause. It is a trigger that will require some filtering. However, you can also do a DELETE TOP 1000. A couple other ways around the trigger itself. It will help in certain cases, but beware of extremely large tables and very big databases. This could definitely slow a system down.
January 25, 2011 at 9:01 am
Nice try but I think unsuspecting users could hit an unintentional side effect. If you table has say n number of rows e.g. n = 1 then updating that row using a a where clause will e.g. UPDATE Table WHERE Table_ID = 5 will not be allowed by your trigger because @@ROWCOUNT = 1 and @Count = 1 ????
So a legitimate update could result in an app failing out there in the field.
Maybe I am mistaken?
January 25, 2011 at 9:01 am
I'm not in front of a server to test thins, but I wonder how this would perform an a large table. It seems to me the delete or update will complete, then rollback. That would require a table lock for, potentially, several minutes.
Thinking out loud, couldn't you create this as an INSTEAD OF trigger and check the input buffer?
SQL guy and Houston Magician
January 25, 2011 at 9:07 am
Agree with others. Better way to solve this is have dev, test, and prod environments. DBAs do code review after developers have submitted code for test. No developer should be running an update against prod.
Also smaller restores are possible by using filegroup backups. This is especially useful on large, partitioned tables. If someone screws up a partition, you can restore just that section while leaving the other sections online. Both of these features are only available on enterprise (filegroup backups and partitioned tables).
-------------------------------------------------------------------------------------------------
My SQL Server Blog
January 25, 2011 at 9:14 am
Just to ask a foolish question, but is that real data?
Are those real names and birthdates?
January 25, 2011 at 9:28 am
This is a cool idea. But it prevents a query from updating/deleting all records from a table. What if i really do want to update/delete all records in a table?
Another approach would be to check the sql that caused the trigger to fire for the presence of the word "where", and also check for and disallow the use of {constant} = {constant} in the where clause as it may be an attempt to fool the trigger, e.g. 1=1. I don't know how this would be technically done, but I'm guessing its doable.
January 25, 2011 at 9:54 am
Just curious, but if every record in the table is satisfied by the WHERE clause, will this still fail the update? (example: if I select rows from {table} where DateOfSale > {somedate}, and every record is greater than {somedate}, will this thwart the update even if it's legitimate?
January 25, 2011 at 9:56 am
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 25, 2011 at 11:09 am
tim.royal (1/25/2011)
Just curious, but if every record in the table is satisfied by the WHERE clause, will this still fail the update? (example: if I select rows from {table} where DateOfSale > {somedate}, and every record is greater than {somedate}, will this thwart the update even if it's legitimate?
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2011 at 11:19 am
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?Are those real names and birthdates?
Well, those are real Indian names probably you may have never heard before. Dunno if it is actual customer data, only the author can tell us.
Thanks,
Amol Naik
January 25, 2011 at 11:24 am
GilaMonster (1/25/2011)
luciano furlan (1/25/2011)
I fear every day, that I will forget the where clause eventually and destroy an entire table.And guess what: Sql doesn't have an Undo button!
If you're doing anything on a production server, the first command in your query window and the first you run should be BEGIN TRANSACTION. That way if you do accidentally leave out (or not select) the where clause, you can roll it back.
Don't undervalue Gails stance about always using this statement; its saved my backside more then once. One time someones kid was at work with them and tehy ran into my office and bumped me whcih natuarally caused me to hit the execute. Thank God the thing started witha BEGIN stamement so it could be easily rolled back.
Another step I take for personal satisfaction is to always do what I call a REPORT ONLY run first and see the changed data and make sure there are no suprises. I make all custom processes that someone can run follow a REPORT ONLY first step so they have no excuse to say "I didn't know it would do that".
Kindest Regards,
Just say No to Facebook!January 25, 2011 at 12:18 pm
Nice article. We are going to use this. The only thing that we are going to add is a caveat to get out of the trigger if the logged on user is a production user. We have production jobs that do update all rows where they turn a 'processed flag' on for all records in a table of successfully processed records.
January 25, 2011 at 12:35 pm
If there's no start transaction specified won't the code fail on the rollback?
January 25, 2011 at 12:45 pm
Gene Porter (1/25/2011)
If there's no start transaction specified won't the code fail on the rollback?
Every INSERT/UPDATE/DELETE statement is it's own implicit transaction. If, for example, that transaction fails 90% through, it will automatically rollback. Also, if you issue a rollback command inside a trigger, that will rollback the transaction.
SQL guy and Houston Magician
Viewing 15 posts - 31 through 45 (of 94 total)
You must be logged in to reply to this topic. Login to reply