Automated Trigger To Require a WHERE Clause

Question

Post reply

Automated Trigger To Require a WHERE Clause

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

jinlye SSC Veteran Points: 230 More actions · Answer 1

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

Slawek Guzek Ten Centuries Points: 1069 More actions · Answer 2

jinlye (1/29/2011)
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.

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.

Slawek Guzek Ten Centuries Points: 1069 More actions · Answer 3

Robert Cary (1/29/2011)
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.

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.

Subhash Kr Singh SSC-Addicted Points: 481 More actions · Answer 4

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) + ''

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

--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) + ''

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

--- 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) + ''

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

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

*/

Rob Fisk SSCommitted Points: 1861 More actions · Answer 5

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.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Rahul The Dba SSChasing Mays Points: 647 More actions · Answer 6

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

[font="Comic Sans MS"]Rahul:-P[/font]

luciano furlan SSC Enthusiast Points: 107 More actions · Answer 7

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.

luciano furlan SSC Enthusiast Points: 107 More actions · Answer 8

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.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 9

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

luciano furlan (2/7/2011)

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

luciano furlan SSC Enthusiast Points: 107 More actions · Answer 11

Christian Buettner-167247 (2/7/2011)

luciano furlan (2/7/2011)
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.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 12

luciano furlan (2/7/2011)

Christian Buettner-167247 (2/7/2011)
luciano furlan (2/7/2011)
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

Michael L John One Orange Chip Points: 27060 More actions · Answer 13

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
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

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

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 15

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?