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]