Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»

Automated Trigger To Require a WHERE Clause Expand / Collapse
Author
Message
Posted Saturday, February 5, 2011 1:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 11, 2013 3:27 PM
Points: 127, Visits: 132
Subhash Kr Singh (1/30/2011)
If I got it wrong then please clarify following:
/* Please run following TSQL query */

CREATE DATABASE TESTING
GO

USE TESTING
GO

CREATE TABLE TESTING
(id INT,
CITY VARCHAR(40),
PIN VARCHAR(6)
)
GO

INSERT INTO TESTING
SELECT 1, 'A','111111'
UNION
SELECT 2, 'B','222222'
UNION
SELECT 3,'C','333333'
UNION
SELECT 4,'D','444444'
GO

/* YOU CODE */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*-- ============================================
-- 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 tools
declare @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 tools
Set @tblname = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))
Set @strTRGText = '' ;
set @errupd = 'Cannot update all rows. Use WHERE CONDITION'; --- UPDATE TRIGGER ERROR MSG
set @errdel = 'Cannot delete all rows. Use WHERE CONDITION'; --- DELETE TRIGGER ERROR MSG
set @errboth = 'Cannot update or delete all rows. Use WHERE CONDITION'; --- UPDATE & DELETE TRIGGER ERROR MSG
set @severity = '16'
set @state = '1'

--if update trigger
if @Type = 'Update'
begin
Set @trgname = '[dbo].[trg_upd_'+ @tblName +']';
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))
begin
Set @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 code

Set @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgname
Set @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblname
Set @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 there
else

begin
Print 'Sorry!! ' + @trgname + ' Already exists in the database... '
end

end


--if delete trigger
else
if @Type = 'Delete'
begin
Set @trgname = '[dbo].[trg_del_'+ @tblName +']';
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))
begin
Set @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 code

Set @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgname
Set @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblname
Set @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 there

else

begin
Print 'Sorry!! ' + @trgname + ' Already exists in the database... '
end

end

--- BOTH THE TRIGGER DELETE & UPDATE
else
if @Type = 'Both'
begin
Set @trgname = '[dbo].[trg_DelUpd_'+ @tblName +']';
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@trgname))
begin
Set @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 code

Set @strTRGText = @strTRGText + CHAR(13) + 'CREATE TRIGGER ' + @trgname
Set @strTRGText = @strTRGText + CHAR(13) + 'ON ' + @tblname
Set @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 there

else

begin
Print 'Sorry!! ' + @trgname + ' Already exists in the database... '
end

end
end

/* YOUR CODE */
GRANT EXECUTE ON SP_Restriction TO PUBLIC

DECLARE @RC int
DECLARE @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
,@Type
GO

/*--------------------------------------------------------*/
/* HERE IS MY T-SQL
I WANT TO UPDATE PIN CODE WITH VALUE '110011' FOR THIS I RUN FOLLOWING QUERY
WHAT 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 1
Cannot update all rows. Use WHERE CONDITION
Msg 3609, Level 16, State 1, Line 1
The 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 CLAUSE
EVEN 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,
*/


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...............



Rahul
Post #1059166
Posted Monday, February 7, 2011 12:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 8:09 AM
Points: 5, Visits: 95
jinlye (1/26/2011)
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.


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.
Post #1059353
Posted Monday, February 7, 2011 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 8:09 AM
Points: 5, Visits: 95
Rob Fisk (1/30/2011)


SELECT m.*
--UPDATE m SET monkey = 'marmosette'
FROM monkeys m
WHERE MonkyID=2



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.
Post #1059355
Posted Monday, February 7, 2011 12:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
Rob Fisk (1/30/2011)


So for an update statement I may write this initially

SELECT m.*
--UPDATE m SET monkey = 'marmosette'
FROM monkeys m
WHERE MonkyID=2



Just make sure you write it like that. I have seen colleagues write like this:


UPDATE m SET monkey = 'marmosette'
SELECT m.*
FROM monkeys m
WHERE MonkyID=2



They 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.


SQL guy and Houston Magician
Post #1059356
Posted Monday, February 7, 2011 12:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
luciano furlan (2/7/2011)
[quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.

No, we don't. Why would I write a where clause when I want all rows returned?


Best Regards,
Chris Büttner
Post #1059363
Posted Monday, February 7, 2011 1:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 8:09 AM
Points: 5, Visits: 95
Christian Buettner-167247 (2/7/2011)
luciano furlan (2/7/2011)
[quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.

No, we don't. Why would I write a where clause when I want all rows returned?

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.
Post #1059369
Posted Monday, February 7, 2011 1:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
luciano furlan (2/7/2011)
Christian Buettner-167247 (2/7/2011)
luciano furlan (2/7/2011)
[quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.

No, we don't. Why would I write a where clause when I want all rows returned?

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.

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.


Best Regards,
Chris Büttner
Post #1059377
Posted Friday, August 9, 2013 3:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:28 AM
Points: 946, Visits: 2,975
Good job on the article, BUT.

If you think that this is a solution, and are planning on relying on this as some sort of protection from stupid, it's not. There are so many holes in this, as well as the potential performance hit, that rolling this out in a production environment would probably be a waste of time.

Sure, you can do this, but why would you want to? There are many things that need to be done first before something like this is deployed. This certainly is a false sense of security.

To state the obvious, why does a developer have anything more than read only rights in a production database?
How did this error get discovered? Did a <fill in big title here> run a report and wonder how the corporate revenue decreased by <fill in big number> overnight? This was an easy one. What if it had been a smaller error that wasn't uncovered for a year or longer?

Lastly, not to pile on, but if you worked for me, you would have been terminated on the spot for publishing production data to a public forum. There's nothing here that can be used in a malicious manner, but it's still production data. As a DBA, protecting the company's data is paramount. In this case, you did not do your job.



Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1482704
Posted Friday, August 9, 2013 1:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 1,787, Visits: 5,697
Interesting, but is it practical to try and protect every table in this way?

In case you are interested - and I am not suggesting this is any better, but for someone like me who works on "other people's systems" it is an occasional watchdog - I have an addin for SSMS 2012 that will alert you if you run a sql statement which updates or deletes and does not include a where clause.

It does not pretend to cover anything other than that simple check but I find it makes me smile when I see it pop up - it just makes me stop and think "do I really want to do this update?"

See my signature for "MMNose" (the name is because I plan to expand it to "sniff" out other bad smells)


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1482912
    Posted Sunday, August 11, 2013 1:54 PM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Sunday, September 29, 2013 1:24 AM
    Points: 429, Visits: 1,721
    If a trigger is going to be used for something like this wouldn't it make more sense to use an INSTEAD OF trigger? Then to get even more granular the trigger could be applied to just specific columns that are most critical?

     
    Post #1483106
    « Prev Topic | Next Topic »

    Add to briefcase «««678910»»

    Permissions Expand / Collapse