Automated Trigger To Require a WHERE Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rahul Kr. Ghosh » Automated Trigger To Require a WHERE Clause

87 posts, Page 8 of 9

«««5 6 7 8 9 »»

Automated Trigger To Require a WHERE Clause

Rate Topic

Display Mode

Topic Options

Author

Message

Slawek Guzek

Posted Saturday, January 29, 2011 4:17 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 3:13 PM
Points: 71, Visits: 158

Subhash Kr Singh (1/28/2011)

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?

Sorry but you seems to be new to English as well..

Article title says: Automated Trigger To Require a WHERE Clause

Troubles with reading or understanding ?

Post #1055743

jinlye

Posted Saturday, January 29, 2011 12:24 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:11 AM
Points: 10, Visits: 149

Subhash Kr Singh (1/28/2011)

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,

Hi Subhash

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

Jinlye

PS Don't be put off asking because of earlier responses.

Post #1055800

Slawek Guzek

Posted Saturday, January 29, 2011 12:46 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 3:13 PM
Points: 71, Visits: 158

jinlye (1/29/2011)

Subhash Kr Singh (1/28/2011)

UPDATE tablename SET name = 'test'

Will it work?

...The straight answer to your question is yes...

PS Don't be put off asking because of earlier responses.

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.

Post #1055806

Robert Michael Cary

Posted Saturday, January 29, 2011 1:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:16 PM
Points: 263, Visits: 269

Slawek Guzek (1/29/2011)

Dear Jinlye, you are wrong, I'm afraid...
That's my humble opinion.

There is nothing humble about your opinion.

If you read 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.

Post #1055817

jinlye

Posted Saturday, January 29, 2011 1:40 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:11 AM
Points: 10, Visits: 149

In context of this article (and applied trigger) the query will NOT work.

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

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

Yes, I guess it is. And nicely put, too.

...and also to answer it.

Ah well - that's nice for Subhash - between us we've answered it for him, whichever way he meant it.

Jinlye

Post #1055821

jinlye

Posted Saturday, January 29, 2011 1:44 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:11 AM
Points: 10, Visits: 149

Sorry but you seems to be new to English as well..

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

Post #1055823

Slawek Guzek

Posted Saturday, January 29, 2011 2:03 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 3:13 PM
Points: 71, Visits: 158

jinlye (1/29/2011)

Sorry but you seems to be new to English as well..

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.

Post #1055828

Slawek Guzek

Posted Saturday, January 29, 2011 2:13 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 3:13 PM
Points: 71, Visits: 158

Robert Cary (1/29/2011)

Dear Robert, apparently you haven't read mine.

Do you really think that Subhash'es question should be asked and also answered here, in this thread, in this forum? Surely you do. I don't.

Jinlye had a valid point - let's not get others bored.

Post #1055831

Subhash Kr Singh

Posted Sunday, January 30, 2011 9:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:44 AM
Points: 176, Visits: 128

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,
*/

Post #1055903

Rob Fisk

Posted Sunday, January 30, 2011 5:04 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163, Visits: 427

Firstly, 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 initially

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

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

_______________________________________________________
Change is inevitable... Except from a vending machine.

Post #1055937

« Prev Topic | Next Topic »

87 posts, Page 8 of 9

«««5 6 7 8 9 »»

Permissions