January 25, 2011 at 12:45 pm
No. Triggers run within the transaction (explicit or implicit) that triggered them.
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 1:16 pm
I ask because posting personal information on a public website would go against anything that a DBA learns.
Even if it is data from India, this should have raised a lot of red flags. Any 'scrubbed data' should made obvious that it is not production data. My guess is that this is production data.
January 25, 2011 at 1:21 pm
aaron-403220 (1/25/2011)
I ask because posting personal information on a public website would go against anything that a DBA learns.Even if it is data from India, this should have raised a lot of red flags. Any 'scrubbed data' should made obvious that it is not production data. My guess is that this is production data.
Totally agree with you. If this is production data then it could lead to a security breach.
Amol Naik
January 25, 2011 at 1:25 pm
What surprises me is that SQL ServerCentral does not have a vetting process to prevent these problems.
January 25, 2011 at 1:28 pm
CirquedeSQLeil (1/25/2011)
Thanks for the article.
you are welcome
[font="Comic Sans MS"]Rahul:-P[/font]
January 25, 2011 at 1:32 pm
Andrew G. Matondo (1/25/2011)
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?
No you are not mistaken it do as you have said ,but this article was written keeping in mind that there is huge table and update and delete (accidental) will affect the database so to stop them i created this code and the article.
But i won't dishearten my reader i am working on this and i will post as soon as i get the solution.......
[font="Comic Sans MS"]Rahul:-P[/font]
January 25, 2011 at 1:34 pm
Robert Cary (1/25/2011)
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?
Thanks a wonderful suggestion ,,, i will use it in my next version
[font="Comic Sans MS"]Rahul:-P[/font]
January 25, 2011 at 1:35 pm
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?Are those real names and birthdates?
What will you do with that ????????
Still i am answering your foolish question, yes it is a real data
[font="Comic Sans MS"]Rahul:-P[/font]
January 25, 2011 at 1:41 pm
Gene Porter (1/25/2011)
If there's no start transaction specified won't the code fail on the rollback?
no
[font="Comic Sans MS"]Rahul:-P[/font]
January 25, 2011 at 2:00 pm
Hi - what if I want to remove all rows from the table and include a valid where clause to remove all rows?
I guess, I will never be able to delete all rows because of this trigger or am I not understanding this clearly?
January 25, 2011 at 3:29 pm
Rahul The Dba (1/25/2011)
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?Are those real names and birthdates?
What will you do with that ????????
Still i am answering your foolish question, yes it is a real data
*Facepalm*
Where do I start...
January 25, 2011 at 3:38 pm
rjohal-500813 (1/25/2011)
Rahul The Dba (1/25/2011)
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?Are those real names and birthdates?
What will you do with that ????????
Still i am answering your foolish question, yes it is a real data
*Facepalm*
Where do I start...
Where does anyone start...? By checking they are not in the list...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 25, 2011 at 9:12 pm
I dont know, you can think of a million different performance impacting ways to stop stupid from happening to your data. Yet, I think the best course of action is to try and keep stupid to their own development system.
January 26, 2011 at 3:54 am
MySQL has had a 'safe updates' feature for years. http://dev.mysql.com/doc/refman/5.1/en/mysql-tips.html#safe-updates So if you are using MySQL and you have safe updates turned on (and you should), 'DELETE FROM MyTable' won't work - it will complain about it not having a WHERE clause. You can still delete all rows if you want, by doing something like 'DELETE FROM MyTable WHERE 1 =1'. It forces you to be explicit about your intention, rather than the only option in Sql Server which is "If you didn't say what rows you wanted it to apply to I'll assume you meant to delete/update them all."
Are you listening Microsoft? SAFE_UPDATES feature is overdue, please.
Yeah yeah I know all that stuff about no ad-hoc queries etc, but it's also true that people shouldn't stick their hands in washing machines when they're spinning so why would we need interlocks on the doors? Because sometimes people do dumb things (never me, of course), and it is smart to build stuff with that in mind.
January 26, 2011 at 11:26 am
TRUNCATE TABLE should wipe the table without DELETE trigger interference...
LH
Viewing 15 posts - 46 through 60 (of 94 total)
You must be logged in to reply to this topic. Login to reply