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

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.

Rob Fisk (1/30/2011)

---

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

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

luciano furlan (2/7/2011)

---

[quote]We need an improvement over Sql language, that doesn't allow writing statements without the where clause.

Christian Buettner-167247 (2/7/2011)

---

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

luciano furlan (2/7/2011)

---

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.