updates are not happening through ssms

  • hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

  • dastagiri16 - Saturday, March 3, 2018 6:22 AM

    hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

    Probably.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin - Saturday, March 3, 2018 6:26 AM

    dastagiri16 - Saturday, March 3, 2018 6:22 AM

    hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

    Probably.

    Hi ,
    Thanks for u r reply..
    I alredy searched a lot and did not find anything about the issue.

    Its straight question ..if we add trigger for a table will it prevent editing the table from edit 200 row option. When we perform delete ,update ,insert through query..it is working as expected..the only problem here is with gui option.

  • dastagiri16 - Saturday, March 3, 2018 6:40 AM

    Phil Parkin - Saturday, March 3, 2018 6:26 AM

    dastagiri16 - Saturday, March 3, 2018 6:22 AM

    hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

    Probably.

    Hi ,
    Thanks for u r reply..
    I alredy searched a lot and did not find anything about the issue.

    Its straight question ..if we add trigger for a table will it prevent editing the table from edit 200 row option. When we perform delete ,update ,insert through query..it is working as expected..the only problem here is with gui option.

    Correct.  It's a very straight question with a very straight answer (which Phil actually provided).  Since everything works correctly after you drop the triggers, it's obviously the triggers that are the problem.  They key is that the SOLUTION is not so straight forward but there's no way for us to help until you post the code to create the table, all the related indexes and constraints, AND the code for the triggers.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Saturday, March 3, 2018 8:13 AM

    dastagiri16 - Saturday, March 3, 2018 6:40 AM

    Phil Parkin - Saturday, March 3, 2018 6:26 AM

    dastagiri16 - Saturday, March 3, 2018 6:22 AM

    hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

    Probably.

    Hi ,
    Thanks for u r reply..
    I alredy searched a lot and did not find anything about the issue.

    Its straight question ..if we add trigger for a table will it prevent editing the table from edit 200 row option. When we perform delete ,update ,insert through query..it is working as expected..the only problem here is with gui option.

    Correct.  It's a very straight question with a very straight answer (which Phil actually provided).  Since everything works correctly after you drop the triggers, it's obviously the triggers that are the problem.  They key is that the SOLUTION is not so straight forward but there's no way for us to help until you post the code to create the table, all the related indexes and constraints, AND the code for the triggers.

    Hi Jeff,
    is trigger  blocking my table updation from gui .is it expected behavior of triggers ? the trigger code is having problem how the updates are happening from update query? That is what my concern.
    I will share my table script and trigger script tomorrow.

  • dastagiri16 - Saturday, March 3, 2018 8:43 AM

    is trigger  blocking my table updation from gui .is it expected behavior of triggers ?

    No.

    It's the behaviour of your specific trigger, which implies that there's a problem with your trigger, but without the code for your trigger, that's all that anyone can say.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Saturday, March 3, 2018 9:05 AM

    dastagiri16 - Saturday, March 3, 2018 8:43 AM

    is trigger  blocking my table updation from gui .is it expected behavior of triggers ?

    No.

    It's the behaviour of your specific trigger, which implies that there's a problem with your trigger, but without the code for your trigger, that's all that anyone can say.

    Hi Team,

    this is my source table syntax

    USE [test1]

    GO

    /****** Object: Table [dbo].[test1]  Script Date: 04/03/2018 9:48:14 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[test1](
        [id] [int] NOT NULL,
        [name] [nchar](10) NULL
    ) ON [PRIMARY]

    GO

    I have created one procedure to create automatically one audit table in destination db and create trigger in source table.

    USE [test1]
    GO
    /****** Object: StoredProcedure [dbo].[usp_Common_Audit]  Script Date: 04/03/2018 9:47:37 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER procedure [dbo].[usp_Common_Audit](@TableName nvarchar(1000),@AuditDBName nvarchar(1000),@SourceDBName nvarchar(1000) )

    as

    begin

    if ((@SourceDBName<>'' or @SourceDBName is not null ) and (@TableName<>'' or @TableName is not null ) and (@AuditDBName<>'' or @AuditDBName is not null))
    begin

    set nocount on;
    SET XACT_ABORT ON;

    BEGIN TRY
    BEGIN TRANSACTION
    -- create this procedure in source server.
    declare @COUNT int
    declare @CNT int
    declare @tcount int
    declare @tCNT int

    -- declare @SourceDBName nvarchar(1000)=''
    -- declare @AuditDBName nvarchar(1000) =''
    -- declare @TableName nvarchar(1000)=''
    declare @Chktblname nvarchar(1000)=''
    declare @InsTbl nvarchar(1000)=''
    DECLARE @AddClms nvarchar(1000)=''
    DECLARE @TrName nvarchar(1000)=''
    declare @TrCheck nvarchar(1000)=''
    set @Chktblname='select @CNT=count(*) From '+@AuditDBName+'.'+'INFORMATION_SCHEMA.TABLES where TABLE_NAME='''+@TableName+'_Audit'+''''
    -- SELECT @Chktblname
    EXECUTE SP_EXECUTESQL @Chktblname ,N'@CNT INT OUTPUT',@CNT=@COUNT OUTPUT

    if( @count) =0
    begin

    set @InsTbl='SELECT * into '+@AuditDBName+'.dbo.'+@TableName+'_Audit ' + 'FROM '+@SourceDBName+'.DBO.'+@TableName +' WHERE 1<>1'
    EXEC( @InsTbl)
    set @AddClms='ALTER TABLE '+@AuditDBName+'.dbo.'+@TableName+'_Audit'+
    ' ADD UserName nvarchar(128) NULL,
        HostName nvarchar(128) NULL,
        AppName nvarchar(1000) NULL,
        UpdateDate datetime DEFAULT (getdate()),
        [Type] [nvarchar](100) NULL,
        AuditID bigint IDENTITY(1,1) NOT NULL'
        exec(@AddClms)
    end

     set @TrCheck='select @tCNT=count(*) From '+@SourceDBName+'.'+'sys.objects where TYPE=''TR'' AND NAME='+'''tr_'+@TableName+'''' EXECUTE SP_EXECUTESQL @TrCheck ,N'@tCNT INT OUTPUT',@tCNT=@tCOUNT OUTPUT
     

    if( @tCOUNT)=0
    begin

    set @TrName=
    'CREATE Trigger '+'tr_'+@TableName+
    ' ON' +' DBO.'+@TableName +' AFTER INSERT ,UPDATE,DELETE
    as
    begin

    declare @Action as char(1)
    set @Action= (case when exists (select * from inserted) and exists (select *From deleted) then ''U''
          when exists (select *From inserted) then ''I''
                 when exists (select *From deleted) then ''D''
             else null end)

    if @Action =''U''
    begin
    insert into '+@AuditDBName+'.dbo.'+@TableName+'_Audit '+' select *,system_user,host_name(),APP_NAME(),getdate(),''Old_Version'' From deleted
    insert into '+@AuditDBName+'.dbo.'+@TableName+'_Audit '+' select *,system_user,host_name(),APP_NAME(),getdate(),''New-Version'' From inserted
    end
    else if @Action =''I''
    begin
    insert into '+@AuditDBName+'.dbo.'+@TableName+'_Audit '+' select *,system_user,host_name(),APP_NAME(),getdate(),''Insert'' From inserted
    end
    else if @Action =''D''
    begin
    insert into '+@AuditDBName+'.dbo.'+@TableName+'_Audit '+' select *,system_user,host_name(),APP_NAME(),getdate(),''Delete'' From Deleted
    end
    end'

    exec(@TrName)

    end
    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT>0    
         SELECT ERROR_NUMBER() AS 'ErrNo',ERROR_MESSAGE() as 'ErrMsg',ERROR_SEVERITY() as 'ErrSeverity',ERROR_STATE() as 'Err_State';
            ROLLBACK TRANSACTION;
    END CATCH

    END

    else begin

    Print 'Provide Correct Parameters'
    end
    end

    steps to execute:
    first we need to create table the we have to execute below command.
    test1 is table name
    we have to pass sourcedb and destinaiondb in the below procedure..

    exec usp_Common_Audit 'test1','sourceDbname','destinationdbname'

  • Since I can't exactly run that, please post the resultant trigger that is on your table?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SSMS and the related "Edit" GUI is nothing more than an application.  In this case, it's like many other applications where it displays some data, allows you to make changes, and then displays those changes.  It displays those changes by reading a result set.  And that's the problem with your trigger code.  While it works just fine if you do a manual update, it returns an unexpected result set because you forgot to include something to suppress that unexpected result set... SET NOCOUNT ON.

    Add that right after the BEGIN in your trigger and you'll be all set.  Let me rephrase that... the trigger will work as you have designed it to work and THAT is a huge problem.

    To make a very long story short, if you look at any of the auditing methods that SQL Server contains natively, NONE OF THEM AUDIT INSERTs and there's a very good reason for that.  Auditing INSERTs produces instant and unnecessary duplication of data.  Consider the following...

    You have an "original" table and a related "audit" table...

    If you do an insert into the original table, where does the original data live?  The answer is, of course, in the original table.  If you've made the mistake of auditing inserts, then you've duplicated that data in your audit table.  If you never make another change to that particular row, the original data persists in both the original table and in the audit table.  Instant and permanent duplication data.  Your 500 GB table now requires 1 Terabyte of disk space, which doubles backup times, doubles restore times, doubles backup to tape times, doubles restore from tape times, doubles any index maintenance you may have, etc, etc.

    Now... look at your trigger.  Not only are you making the mistake of auditing inserts, but you're duplicating even more data by auditing both the INSERTED and DELETED logical tables.  For any given row, you're saving both the previous condition and the new condition of the row during an UPDATE.  So, after you've stored a duplicate of the data during an INSERT (1st dup), you do an UPDATE, which saves another copy of the original insert and saves a copy of the current row (INSERTED), the latter of which can also be found in the original table.  So, with just 1 update, you end up with 3 full rows in the audit table.  Now your 500GB table needs 1.5 TB of storage if each row that you insert suffers just one update each.  Each and every update will cause yet another duplicate row because of the INSERTED logical table.

    I recommend that you audit only the DELETED logical table with the understanding that the latest version of any given row will always be in the original table and that the "old" rows will be in the audit table.

    Bottom line... only store old values which are in the DELETED logic table.  You DBA, SAN Admin, and Backup Admin will love you lot's more than they will with your current trigger.

    Also remember that LOBs are NOT directly auditable through the logical tables and, if that requirement exists, you'll need to make some serious changes to your trigger code.

    Last but not least, take some pride in your code.  Add a correctly annotated "flower box" to both the stored procedure and resulting trigger code, take some time to standardize capitalization and indentation, and add some bloody comments to help people troubleshoot the code in the future.  I don't care if this is POP code (Proof-of-Principle) because if the code ends up working, you or someone else will find some excuse to not do those things and you'll end up with crappy looking and difficult to maintain code in production.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • p.s.  Your CATCH block is in serious need of some help, as well.  It doesn't even identify which line of the trigger caused a problem. 

    You also have mismatched datatypes for parameters (they should be NVARCHAR(128) to match the system tables/views, you hit on the INSERTED and DELETED tables way too many times just trying to figure out what type of trigger action caused the trigger to fire, and you should consider using system intrinsic functions instead of doing SELECTs from system objects to determine if something exists or not, which will inherently validate whether the input parameters are correct or not.

    Also, your code is very susceptible to SQL Injection.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Phil Parkin - Saturday, March 3, 2018 6:26 AM

    dastagiri16 - Saturday, March 3, 2018 6:22 AM

    hi,
    I have created after insert ,update,delete trigger for one of the table.The table does not have any primary key.When we are trying to update the table in edit top 200 rows ..it is preventing to update the records..when i drop the trigger it is allowing..is the anything i missed?

    Probably.

    gold

  • Jeff Moden - Sunday, March 4, 2018 12:09 PM

    p.s.  Your CATCH block is in serious need of some help, as well.  It doesn't even identify which line of the trigger caused a problem. 

    You also have mismatched datatypes for parameters (they should be NVARCHAR(128) to match the system tables/views, you hit on the INSERTED and DELETED tables way too many times just trying to figure out what type of trigger action caused the trigger to fire, and you should consider using system intrinsic functions instead of doing SELECTs from system objects to determine if something exists or not, which will inherently validate whether the input parameters are correct or not.

    Also, your code is very susceptible to SQL Injection.

    Thanks Jeff,

    I will add and let u know.

    One of the organization requested that audit requirement.So I did like that.i told that space and performance issues..but they want all audit.

  • dastagiri16 - Sunday, March 4, 2018 9:12 PM

    Jeff Moden - Sunday, March 4, 2018 12:09 PM

    p.s.  Your CATCH block is in serious need of some help, as well.  It doesn't even identify which line of the trigger caused a problem. 

    You also have mismatched datatypes for parameters (they should be NVARCHAR(128) to match the system tables/views, you hit on the INSERTED and DELETED tables way too many times just trying to figure out what type of trigger action caused the trigger to fire, and you should consider using system intrinsic functions instead of doing SELECTs from system objects to determine if something exists or not, which will inherently validate whether the input parameters are correct or not.

    Also, your code is very susceptible to SQL Injection.

    Thanks Jeff,

    I will add and let u know.

    One of the organization requested that audit requirement.So I did like that.i told that space and performance issues..but they want all audit.

    Thanks for the feedback.  Them ignoring the performance and space issues is disappointing and something that they'll end up paying dearly for in the future but, hopefully, you're out of the woods on this problem now.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Monday, March 5, 2018 8:36 PM

    dastagiri16 - Sunday, March 4, 2018 9:12 PM

    Jeff Moden - Sunday, March 4, 2018 12:09 PM

    p.s.  Your CATCH block is in serious need of some help, as well.  It doesn't even identify which line of the trigger caused a problem. 

    You also have mismatched datatypes for parameters (they should be NVARCHAR(128) to match the system tables/views, you hit on the INSERTED and DELETED tables way too many times just trying to figure out what type of trigger action caused the trigger to fire, and you should consider using system intrinsic functions instead of doing SELECTs from system objects to determine if something exists or not, which will inherently validate whether the input parameters are correct or not.

    Also, your code is very susceptible to SQL Injection.

    Thanks Jeff,

    I will add and let u know.

    One of the organization requested that audit requirement.So I did like that.i told that space and performance issues..but they want all audit.

    Thanks for the feedback.  Them ignoring the performance and space issues is disappointing and something that they'll end up paying dearly for in the future but, hopefully, you're out of the woods on this problem now.

    Thanks Jeff, after adding set nocount ..I m able to delete the records.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply