InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:21:06 GMT20http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]luciano furlan (2/7/2011)[/b][hr][quote][b]Christian Buettner-167247 (2/7/2011)[/b][hr][quote][b]luciano furlan (2/7/2011)[/b][hr][quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.[/quote]No, we don't. Why would I write a where clause when I want all rows returned?[/quote]Your opinion is correct if you think only about "select" statements.But when you think about update/delete statements, it is intuitive, that more dangerous is the action "longer" should be the code to write, not the opposite.[/quote]You sometimes also have to update or delete all rows.In turn I may ask you: How often have you accidentally updated or deleted all rows instead of some rows? And compared to that: How often have you updated or deleted the wrong rows?I am usually not against safe-guards to prevent common errors, but how often do you "forget" a where clause? If I look back in my life, I have done a lot of stupid things, but I never "forgot" a where clause, when I wanted to select / update or delete only a subset of rows. Instead I had maybe used the wrong criteria, which leads to the same result as a missing where clause: Incorrect data.You need to revise such things very carefully, before you overload the language with safeguards. The benefit should outweigh the cost significantly, otherwise it may be better to keep the status quo.Mon, 07 Feb 2011 01:20:37 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Christian Buettner-167247 (2/7/2011)[/b][hr][quote][b]luciano furlan (2/7/2011)[/b][hr][quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.[/quote]No, we don't. Why would I write a where clause when I want all rows returned?[/quote]Your opinion is correct if you think only about "select" statements.But when you think about update/delete statements, it is intuitive, that more dangerous is the action "longer" should be the code to write, not the opposite.Mon, 07 Feb 2011 01:05:40 GMTluciano furlanhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]luciano furlan (2/7/2011)[/b][hr][quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.[/quote]No, we don't. Why would I write a where clause when I want all rows returned?Mon, 07 Feb 2011 00:55:44 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Rob Fisk (1/30/2011)[/b][hr]So for an update statement I may write this initiallySELECT m.*--UPDATE m SET monkey = 'marmosette'FROM monkeys mWHERE MonkyID=2[/quote]Just make sure you write it like that. I have seen colleagues write like this:UPDATE m SET monkey = 'marmosette'SELECT m.*FROM monkeys mWHERE MonkyID=2They would select the last three lines and execute the selection then delete the select and execute the update. Unfortunately, if you forget to delete the select you end up with two statements executing. Mon, 07 Feb 2011 00:36:46 GMTRobert Michael Caryhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Rob Fisk (1/30/2011)[/b][hr]SELECT m.*--UPDATE m SET monkey = 'marmosette'FROM monkeys mWHERE MonkyID=2[/quote]That's the same way I always write updates/deletes. It is not perfect, but it is safer.By the way it is interesting, that this type of syntax works well in mssql, but doesn't in other Sql dialects. Aliases after Update/Delete words seems to not be sql 92 standard.Mon, 07 Feb 2011 00:27:33 GMTluciano furlanhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]jinlye (1/26/2011)[/b][hr]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.[/quote]That's what I am talking about! We need an improvement over Sql language, that doesn't allow writing statements without the where clause.In Microsoft Sql, I mean.And for the sake of back compatibility it shoul be OPTIONal.Mon, 07 Feb 2011 00:15:46 GMTluciano furlanhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Subhash Kr Singh (1/30/2011)[/b][hr]If I got it wrong then please clarify following:/* Please run following TSQL query */CREATE DATABASE TESTINGGOUSE TESTINGGOCREATE TABLE TESTING (id INT, CITY VARCHAR(40), PIN VARCHAR(6) )GOINSERT INTO TESTINGSELECT 1, 'A','111111'UNIONSELECT 2, 'B','222222'UNIONSELECT 3,'C','333333'UNIONSELECT 4,'D','444444'GO/* YOU CODE */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*-- ============================================-- Author : Rahul Kr. Ghosh : MCITP-DBA 2009-- Create date : Sep 20 2010 1:34PM-- Description : STOPPING ALL ROWS UPDATE, DELETE AT ONCE-- @Type : 'Update' if to create only update trigger : 'Delete' if to create only delete trigger : 'Both' if to create both (combine) delete & update trigger-- ============================================= */CREATE PROC [dbo].[SP_Restriction]--getting table name & type of trigger@tblname varchar(50),@Type varchar(20)as begin--getting my toolsdeclare @trgname nvarchar(50)Declare @strTRGText Varchar(max)declare @errdel varchar(50)declare @errupd varchar(50)declare @errboth varchar(60)declare @severity nvarchar(5)declare @state nvarchar (5)--setting my toolsSet @tblname = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))Set @strTRGText = '' ;set @errupd = 'Cannot update all rows. Use WHERE CONDITION'; --- UPDATE TRIGGER ERROR MSGset @errdel = 'Cannot delete all rows. Use WHERE CONDITION'; --- DELETE TRIGGER ERROR MSGset @errboth = 'Cannot update or delete all rows. Use WHERE CONDITION'; --- UPDATE & DELETE TRIGGER ERROR MSGset @severity = '16'set @state = '1'--if update triggerif @Type = 'Update'begin Set @trgname = '[dbo].[trg_upd_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE UPDATE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the update trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR UPDATE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errupd + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (update)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endend--if delete triggerelse if @Type = 'Delete'beginSet @trgname = '[dbo].[trg_del_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE DELETE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the delete trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR DELETE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errdel + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (delete)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endend--- BOTH THE TRIGGER DELETE & UPDATE else if @Type = 'Both'beginSet @trgname = '[dbo].[trg_DelUpd_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE UPDATE AND DELETE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the delete & UPDATE trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR UPDATE , DELETE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errboth + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (update & delete)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endendend/* YOUR CODE */GRANT EXECUTE ON SP_Restriction TO PUBLICDECLARE @RC intDECLARE @tblname varchar(50)DECLARE @Type varchar(20)-- TODO: Set parameter values here.SET @tblname = 'TESTING'SET @Type = 'UPDATE'EXECUTE @RC = [TESTING].[dbo].[SP_Restriction] @tblname ,@TypeGO/*--------------------------------------------------------*//* HERE IS MY T-SQL I WANT TO UPDATE PIN CODE WITH VALUE '110011' FOR THIS I RUN FOLLOWING QUERYWHAT IS WRONG WITH THIS.*/UPDATE TESTING SET PIN = '110011' where 1=1 /* it gives error Msg 50000, Level 16, State 1, Procedure trg_upd_TESTING, Line 1Cannot update all rows. Use WHERE CONDITIONMsg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.Here I have used where clause even then my statement fails. Here I only want to know as describe by you that query withOUT WHERE WILL NOT RUN. bUT HERE I HAVE USED WHERE CLAUSEEVEN THEN IT FAILS.BECAUSE YOU HAVE NOT VERIFY ANYWHERE THAT WHETHER THIS QUERY USES 'WHERE' OR NOT. SIMPLY YOU USES THE @@ROWCOUNT.Please correct my query if I am doing anything wrong. Because as by you i have created trigger as instructed by you and running my query to update the 'PIN' but it is getting failes.Thanks,*/[/quote]HI..Subhash you are new to SQL SERVER that's ok but i guess you are new to ENGLISH also, in my article i had clearly mentioned that no delete or update will be made if you are not using a WHERE condition **A VALID WHERE CONDITION**, what you are doing you TSQL--- UPDATE TESTING SET PIN = '110011' where 1=1 is this where condition (where 1=1) is valid i guess not, you are making changes to all rows my article is here not only to make the use of where condtion mandatory but also to stop all row changes which you are doing it should be like this---UPDATE TESTING SET PIN = '110011' where pin = '111111' you cannot update all rows plssss get the meaning of this article and stop asking the same question over and over..........Thanks...............Sat, 05 Feb 2011 13:49:13 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxFirstly, I'll belatedly add my congratulations for a clear and concise article. It was well written and explained the intent despite the misleading title about the where clause rather than max rows. I've been following the comments ever somce I thought "hmm, a handy emergency brake but does it work and what are the workarounds and deficits?"Personally I don't aftee with such an approach as a warning since accidentally wiping a live, or near live system is the ultimate way to scare sql developers into better practices.I know. I was very lucky when I wiped out the email ody text for every email our support staff has spent 4 weeks entering in 14 languages due to a highlightuing of code glitch.. Luckilly another developer had backed up the DB for personal use only 10 minutes before but it could have been bad.I now train myself to write queries in a certain way so that you first build the select then the update/delete and if you are anywhere other than your personal dev (ITS, UAT or Live) then you never run an update query without a budy. The budy system works great. No matter how simple the query you sit at the screen and talk them though it at each stage before executinng. Anything too complicated to talk through should be part of a tested release.So for an update statement I may write this initiallySELECT m.*--UPDATE m SET monkey = 'marmosette'FROM monkeys mWHERE MonkyID=2Then just highlight from the coment to run the script but allways, always have another by my side explaining every step and giving me the OK.It is though a marvelous example of auto trigger creation for tables whcich could be use to create audit tables. I am sure that has been covered before though.Sun, 30 Jan 2011 17:04:41 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxIf I got it wrong then please clarify following:/* Please run following TSQL query */CREATE DATABASE TESTINGGOUSE TESTINGGOCREATE TABLE TESTING (id INT, CITY VARCHAR(40), PIN VARCHAR(6) )GOINSERT INTO TESTINGSELECT 1, 'A','111111'UNIONSELECT 2, 'B','222222'UNIONSELECT 3,'C','333333'UNIONSELECT 4,'D','444444'GO/* YOU CODE */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*-- ============================================-- Author : Rahul Kr. Ghosh : MCITP-DBA 2009-- Create date : Sep 20 2010 1:34PM-- Description : STOPPING ALL ROWS UPDATE, DELETE AT ONCE-- @Type : 'Update' if to create only update trigger : 'Delete' if to create only delete trigger : 'Both' if to create both (combine) delete & update trigger-- ============================================= */CREATE PROC [dbo].[SP_Restriction]--getting table name & type of trigger@tblname varchar(50),@Type varchar(20)as begin--getting my toolsdeclare @trgname nvarchar(50)Declare @strTRGText Varchar(max)declare @errdel varchar(50)declare @errupd varchar(50)declare @errboth varchar(60)declare @severity nvarchar(5)declare @state nvarchar (5)--setting my toolsSet @tblname = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))Set @strTRGText = '' ;set @errupd = 'Cannot update all rows. Use WHERE CONDITION'; --- UPDATE TRIGGER ERROR MSGset @errdel = 'Cannot delete all rows. Use WHERE CONDITION'; --- DELETE TRIGGER ERROR MSGset @errboth = 'Cannot update or delete all rows. Use WHERE CONDITION'; --- UPDATE & DELETE TRIGGER ERROR MSGset @severity = '16'set @state = '1'--if update triggerif @Type = 'Update'begin Set @trgname = '[dbo].[trg_upd_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE UPDATE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the update trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR UPDATE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errupd + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (update)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endend--if delete triggerelse if @Type = 'Delete'beginSet @trgname = '[dbo].[trg_del_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE DELETE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the delete trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR DELETE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errdel + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (delete)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endend--- BOTH THE TRIGGER DELETE & UPDATE else if @Type = 'Both'beginSet @trgname = '[dbo].[trg_DelUpd_'+ @tblName +']';IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))beginSet @strTRGText = @strTRGText + CHAR(13) + '' Set @strTRGText = @strTRGText + CHAR(13) + '/*-- =============================================' Set @strTRGText = @strTRGText + CHAR(13) + '-- Author : Rahul Kr. Ghosh' Set @strTRGText = @strTRGText + CHAR(13) + ' : MCITP-DBA 2009' Set @strTRGText = @strTRGText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate()) Set @strTRGText = @strTRGText + CHAR(13) + '-- Description : STOPPING THE UPDATE AND DELETE OF ALL ROWS AT A STRESS IN TABLE ' + @tblName Set @strTRGText = @strTRGText + CHAR(13) + '-- ============================================= */'-- creating the delete & UPDATE trigger codeSet @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgnameSet @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblnameSet @strTRGText = @strTRGText + CHAR(13) + 'FOR UPDATE , DELETE AS'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'DECLARE @Count int'Set @strTRGText = @strTRGText + CHAR(13) + 'SET @Count = @@ROWCOUNT;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'IF @Count >= (SELECT SUM(row_count)' Set @strTRGText = @strTRGText + CHAR(13) + 'FROM sys.dm_db_partition_stats'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'WHERE OBJECT_ID = OBJECT_ID(''' + @tblname + ''')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'BEGIN'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RAISERROR('''+ @errboth + ''',' + @severity +',' + @state +')'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'ROLLBACK TRANSACTION' Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'RETURN;'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + 'END'Set @strTRGText = @strTRGText + CHAR(13) + ''Set @strTRGText = @strTRGText + CHAR(13) + ''Exec(@strTRGText);print 'Trigger done (update & delete)' if (@@ERROR=0) Print 'Trigger ' + @trgname + ' Created Successfully 'end--trigger already thereelsebeginPrint 'Sorry!! ' + @trgname + ' Already exists in the database... 'endendend/* YOUR CODE */GRANT EXECUTE ON SP_Restriction TO PUBLICDECLARE @RC intDECLARE @tblname varchar(50)DECLARE @Type varchar(20)-- TODO: Set parameter values here.SET @tblname = 'TESTING'SET @Type = 'UPDATE'EXECUTE @RC = [TESTING].[dbo].[SP_Restriction] @tblname ,@TypeGO/*--------------------------------------------------------*//* HERE IS MY T-SQL I WANT TO UPDATE PIN CODE WITH VALUE '110011' FOR THIS I RUN FOLLOWING QUERYWHAT IS WRONG WITH THIS.*/UPDATE TESTING SET PIN = '110011' where 1=1 /* it gives error Msg 50000, Level 16, State 1, Procedure trg_upd_TESTING, Line 1Cannot update all rows. Use WHERE CONDITIONMsg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.Here I have used where clause even then my statement fails. Here I only want to know as describe by you that query withOUT WHERE WILL NOT RUN. bUT HERE I HAVE USED WHERE CLAUSEEVEN THEN IT FAILS.BECAUSE YOU HAVE NOT VERIFY ANYWHERE THAT WHETHER THIS QUERY USES 'WHERE' OR NOT. SIMPLY YOU USES THE @@ROWCOUNT.Please correct my query if I am doing anything wrong. Because as by you i have created trigger as instructed by you and running my query to update the 'PIN' but it is getting failes.Thanks,*/Sun, 30 Jan 2011 09:33:19 GMTSubhash Kr Singhhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Robert Cary (1/29/2011)[/b][hr]There is nothing humble about your opinion. If you [i]read[/i] his post, you will see he is correct and is actually taking the time to explain his answer (you seemed to have edited out the explanation in context to the article and replaced it with an ellipsis.) You, however, do not offer any coherent explanation and simply ask the poster if he is stupid in about as many words. If you would prefer to take portions of the post out of context to 'prove' you're right, have at it, but you are simply embarrassing yourself on this forum.[/quote]Dear Robert, apparently you haven't read mine.Do you really think that Subhash'es question should be asked and also answered [b]here[/b], in [b]this [/b]thread, in [b]this [/b]forum? Surely you do. I don't. Jinlye had a valid point - let's not get others bored.Sat, 29 Jan 2011 14:13:58 GMTSlawek Guzekhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]jinlye (1/29/2011)[/b][hr][quote]Sorry but you seems to be new to English as well..[/quote]Sorry to do this, but I have to point out as an English speaker, that should be 'you seem', not 'you seems'. Although I accept that this may not be the right forum for points relating to English grammar.[/quote]Dear Jinlye, You are most welcome. Really.English is not my native language and I am always happy if someone points out my errors (also those not language related)This is the only way I can improve my English. And learn something new.Sat, 29 Jan 2011 14:03:08 GMTSlawek Guzekhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote]Sorry but you seems to be new to English as well..[/quote]Sorry to do this, but I have to point out as an English speaker, that should be 'you seem', not 'you seems'. Although I accept that this may not be the right forum for points relating to English grammar.I'm out of this thread now, because we'll be boring other people...Sat, 29 Jan 2011 13:44:56 GMTjinlyehttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote]In context of this article (and applied trigger) the query will NOT work.[/quote]Ah. Good point. Subhash's question could have been meant in one of two ways: 1) Will this query work generally (yes, affecting all rows), or 2) Will this query work if I have Rahul's trigger installed (no, it will give the error message raised by the trigger and roll back).[quote]In context of answering general questions about such a basic SQL queries like this - this is wrong thread and/or wrong forum to ask such a question[/quote]Yes, I guess it is. And nicely put, too.[quote]...and also to answer it.[/quote]Ah well - that's nice for Subhash - between us we've answered it for him, whichever way he meant it.JinlyeSat, 29 Jan 2011 13:40:21 GMTjinlyehttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Slawek Guzek (1/29/2011)[/b][hr]Dear Jinlye, you are wrong, I'm afraid...That's my humble opinion.[/quote]There is nothing humble about your opinion. If you [i]read[/i] his post, you will see he is correct and is actually taking the time to explain his answer (you seemed to have edited out the explanation in context to the article and replaced it with an ellipsis.) You, however, do not offer any coherent explanation and simply ask the poster if he is stupid in about as many words. If you would prefer to take portions of the post out of context to 'prove' you're right, have at it, but you are simply embarrassing yourself on this forum.Sat, 29 Jan 2011 13:31:54 GMTRobert Michael Caryhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]jinlye (1/29/2011)[/b][hr][quote][b]Subhash Kr Singh (1/28/2011)[/b][hr]UPDATE tablename SET name = 'test'Will it work?[/quote]...The straight answer to your question is yes...PS Don't be put off asking because of earlier responses.[/quote]Dear Jinlye, you are wrong, I'm afraid. In context of this article (and applied trigger) the query will NOT work.In context of answering general questions about such a basic SQL queries like this - this is wrong thread and/or wrong forum to ask such a question and also to answer it.That's my humble opinion.Sat, 29 Jan 2011 12:46:07 GMTSlawek Guzekhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Subhash Kr Singh (1/28/2011)[/b][hr]Hi Rahul,I am just new to sql server. But I just want to know one simple question what will be the statement if i want to update a particular column of all rows i.e.UPDATE tablename SET name = 'test'Will it work?Thanks,[/quote]Hi SubhashBest way to answer questions like this is to have a go! SQL Server Express is free, and the syntax is the same as all the paid-for versions. Set up a test environment and try some things out.The straight answer to your question is yes, what you have written will update *all* the rows. That leads to the point of this article - if in standard SQL you issue an update or delete command without specifying which rows you want it to apply to (i.e. without a WHERE clause), it will indeed update all the rows. It is an easy mistake to make - to forget the WHERE clause and then end up trashing all the rows - which is why the author of the article we're discussing came up with a (flawed) trigger to try to prevent that.JinlyePS Don't be put off asking because of earlier responses.Sat, 29 Jan 2011 12:24:33 GMTjinlyehttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Subhash Kr Singh (1/28/2011)[/b][hr]Hi Rahul,I am just new to sql server. But I just want to know one simple question what will be the statement if i want to update a particular column of all rows i.e.[b]UPDATE tablename SET name = 'test'[/b]Will it work?[/quote]Sorry but you seems to be new to English as well..Article title says: [b]Automated Trigger To Require a WHERE Clause[/b]Troubles with reading or understanding ?Sat, 29 Jan 2011 04:17:18 GMTSlawek Guzekhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxHi Rahul,I am just new to sql server. But I just want to know one simple question what will be the statement if i want to update a particular column of all rows i.e.UPDATE tablename SET name = 'test'Will it work?Thanks,Fri, 28 Jan 2011 22:29:17 GMTSubhash Kr Singhhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxThank you rjohal-500813 and ta.bu.shi.da.yu.As an university employee at a hospital in Canada, I too would be fired and the provincial privacy office would be asking serious questions about our project.Thu, 27 Jan 2011 12:40:11 GMTaaron-403220http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Carlo Romagnano (1/25/2011)[/b][hr][quote][b]luciano furlan (1/25/2011)[/b][hr]It is a smart workaround, but it doesn't really solve the problem in an elegant way.I miss a configuration on Sql server that states "Where Clause mandatory". For all tables, for all Sql statements.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!But still if you need (and you will need) you can write "Where 1=1", to update/delete every record in a table.Your workaround treats it like a "missing where".You can see the difference between an accidentaly forgoten where and one you written on purpose.I believe this is a huge "security hole" in the Sql language. And yet it stays with us for so many years (and so many tears).[/quote]I disagree: what about using transactions?In general, this trigger affects performance just to prevent programmers errors.Before any DDL or MDL I use the fantastic "SET IMPLICIT_TRANSACTIONS ON".and only when I sure of modification I run the COMMIT command.[/quote]I think the following DELETE trigger migth do the trick. I am not skilled with PATINDEX so that part can properly better, but the DELETE trigger will catch the current statement. The script is for MS-SQL2008R2, but MS-SQL2005 should also work.I do not recommend this method on a table with heavy (delete) traffic since the in-memory table is expensive.Those word said, here is my suggestion (the DELETE trigger is in [b]Bold[/b], the other stuff is just for testing):[code="plain"]-- ====================-- Create a test table-- ====================create table foo( field1 varchar(10));GO[b]-- ===================================-- Create DELETE trigger on table foo-- ===================================CREATE TRIGGER tr_foo_deleteON fooFOR DELETEASDECLARE @T TABLE ( language_event nvarchar(100), parameters INT, event_info nvarchar(4000) ,event_time datetime DEFAULT CURRENT_TIMESTAMP);DECLARE @pi int;DECLARE @errmsg varchar(300);INSERT INTO @T( language_event ,parameters ,event_info --,event_time )EXEC ('DBCC INPUTBUFFER(@@SPID);');-- ==============================================-- TEST for expression ...WHERE columnname = ...-- ==============================================SELECT @pi = PATINDEX('%WHERE%[a-z]%=%_', (SELECT TOP 1 event_info from @T))IF @pi = 0BEGIN -- ============================================== -- TEST for expression ...WHERE ? = columnname... -- ============================================== SELECT @pi = PATINDEX('%WHERE%=%[a-z]', (SELECT TOP 1 event_info from @T));END-- ================================================-- If a (poor man) match was not found raise error-- ================================================IF @pi = 0BEGIN SELECT TOP 1 @errmsg = 'Delete without valid WHERE is prohibited: ' + Left(event_info, 200) from @T; RAISERROR (@errmsg, 16, 1); ROLLBACK TRANSACTION;ENDGO[/b]-- ================================================-- Test the DELETE trigger-- ================================================INSERT into foo (field1) values ('abcd');INSERT into foo (field1) values ('1');GODELETE FROM foo;GOSELECT 1, 'DELETE is catched', * FROM foo;GODELETE FROM foo WHERE 1 = 1;GOSELECT 2, 'DELETE is catched', * FROM foo;GODELETE FROM foo WHERE field1 = '1';GOSELECT 3, 'DELETE is valid', * FROM foo;GODELETE FROM foo where field1 = 'abcd';GOSELECT 4, 'DELETE is valid', * FROM fooUNIONSELECT 4, 'DELETE is valid', '';GODROP TABLE foo;GO[/code]Kind regards LHThu, 27 Jan 2011 11:27:03 GMTlars.hesselberghttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxThat is an excellant point, what if you were checking if there was actually a "WHERE" clause in the SQL statment it could be "WHERE 1 = 1". I have seen people use this code. :-)Thu, 27 Jan 2011 08:24:25 GMTdd-1011661http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxAvoid beginners to update production table is the better way to avoid disaster!In the author concept thers's a BIG BUG!If before the trigger as described in the article another trigger is called the @@rowcount may be modified and the check will fail.Remember: @@ROWCOUNT is countinuosly modified from SELECT, UPDATE, INSERT, DELETE.When you create the trigger as in the article, you should be sure that your trigger is called first. [code="sql"]SET @Count = @@ROWCOUNT; -- Are you sure that this trigger is called first? Another one may modify @@rowcountIF @Count >= (SELECT SUM(row_count)FROM sys.dm_db_partition_statsWHERE OBJECT_ID = OBJECT_ID('table'))[/code]Here a simple example of the BUG.[code="sql"]create table tmptmp(a sysname primary key)insert tmptmp select name from sys.objectsgocreate trigger tr_tmptmp on tmptmpfor updateasbegin select 1 -- modify @@rowcount print @@rowcountendgocreate trigger tr_tmptmp_1 on tmptmpfor updateasbegin print @@rowcountendgoupdate tmptmp set a = a[/code]Thu, 27 Jan 2011 01:28:57 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxPerhaps this has been already said but in my opinion author makes VERY important FALSE assumption.The condition @@ROWCOUNT < table/index/statistic COUNT has NOTHING in common with presence or not WHERE clause in SQL Query. Prove: UPDATE Whatever WHERE 1=1So, since fundamental assumption of solution is PLAIN WRONG, solution based on it can not be correct. It is nice problem workaround, It might work, might work even in most cases, but IMHO correct solution is NOT to allow developer to run untested queries against production server.... Or - retrieve original query text from DMV and test for presence WHERE clause in query text... Wed, 26 Jan 2011 15:46:31 GMTSlawek Guzekhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]ta.bu.shi.da.yu (1/26/2011)[/b][hr][quote][b]Rahul The Dba (1/25/2011)[/b][hr][quote][b]aaron-403220 (1/25/2011)[/b][hr]Just to ask a foolish question, but is that real data?Are those real names and birthdates?[/quote]What will you do with that ????????Still i am answering your foolish question, yes it is a real data[/quote]You should be fired for your incredible incompetence and stupidity.[/quote]The above comment may sound harsh, however the poster is correct.You have shown a flagrant disregard for data security. Is this data sourced from your employer? In the UK, we have Data Protection laws. If I was to have exposed 'real' data as you have, I would be fired and my employer fined.As a DBA, you are the guardian of the data. You however have shown a very laxadaisical attitude to the trust placed in you.Your comment above also indicates that you don't realise what you have done wrong. That makes it worse as you consider yourself a dba.Wed, 26 Jan 2011 15:45:43 GMTDwayne Dibleyhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Rahul The Dba (1/25/2011)[/b][hr][quote][b]aaron-403220 (1/25/2011)[/b][hr]Just to ask a foolish question, but is that real data?Are those real names and birthdates?[/quote]What will you do with that ????????Still i am answering your foolish question, yes it is a real data[/quote]You should be fired for your incredible incompetence and stupidity.Wed, 26 Jan 2011 15:15:37 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxThanks LH. I thought of the Truncate table after I posted my doubt on the requirement to Delete all rows (with these triggers on). Thanks for your confirmation.Wed, 26 Jan 2011 11:57:55 GMTmpb-543430http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxI think this is false advertising. You are really NOT stopping an update or delete statement without a "WHERE" clause from running, you are just comparing the number of rows affected to the number of rows in the table, or maybe not, as others have been added concerns about index's adding to the rowcount. What if someone actually does need to update all the rows in the table. What if the update statement does have a "WHERE" clause and all of the table rows meet the criteria of the where clause. I can see this creating problems, especially down the road when someone else is maintaing the database. I would not recommend using this code, sorry.Wed, 26 Jan 2011 11:40:45 GMTdd-1011661http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxTRUNCATE TABLE should wipe the table without DELETE trigger interference...LHWed, 26 Jan 2011 11:26:32 GMTlars.hesselberghttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxMySQL 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.Wed, 26 Jan 2011 03:54:13 GMTjinlyehttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxI 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.Tue, 25 Jan 2011 21:12:02 GMTmferandahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]rjohal-500813 (1/25/2011)[/b][hr][quote][b]Rahul The Dba (1/25/2011)[/b][hr][quote][b]aaron-403220 (1/25/2011)[/b][hr]Just to ask a foolish question, but is that real data?Are those real names and birthdates?[/quote]What will you do with that ????????Still i am answering your foolish question, yes it is a real data[/quote]*Facepalm*Where do I start...[/quote]Where does anyone start...? By checking they are not in the list...Tue, 25 Jan 2011 15:38:23 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Rahul The Dba (1/25/2011)[/b][hr][quote][b]aaron-403220 (1/25/2011)[/b][hr]Just to ask a foolish question, but is that real data?Are those real names and birthdates?[/quote]What will you do with that ????????Still i am answering your foolish question, yes it is a real data[/quote]*Facepalm*Where do I start...Tue, 25 Jan 2011 15:29:50 GMTDwayne Dibleyhttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxHi - 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?Tue, 25 Jan 2011 14:00:17 GMTmpb-543430http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Gene Porter (1/25/2011)[/b][hr]If there's no start transaction specified won't the code fail on the rollback?[/quote]noTue, 25 Jan 2011 13:41:14 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]aaron-403220 (1/25/2011)[/b][hr]Just to ask a foolish question, but is that real data?Are those real names and birthdates?[/quote]What will you do with that ????????Still i am answering your foolish question, yes it is a real dataTue, 25 Jan 2011 13:35:46 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Robert Cary (1/25/2011)[/b][hr]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?[/quote]Thanks a wonderful suggestion ,,, i will use it in my next versionTue, 25 Jan 2011 13:34:40 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]Andrew G. Matondo (1/25/2011)[/b][hr]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?[/quote]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.......Tue, 25 Jan 2011 13:32:05 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]CirquedeSQLeil (1/25/2011)[/b][hr]Thanks for the article.[/quote]you are welcomeTue, 25 Jan 2011 13:28:13 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspxWhat surprises me is that SQL ServerCentral does not have a vetting process to prevent these problems.Tue, 25 Jan 2011 13:25:37 GMTaaron-403220http://www.sqlservercentral.com/Forums/Topic1052832-2866-1.aspx[quote][b]aaron-403220 (1/25/2011)[/b][hr]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.[/quote]Totally agree with you. If this is production data then it could lead to a security breach.Tue, 25 Jan 2011 13:21:31 GMTAmolNaik