• 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]