SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


updates are not happening through ssms


updates are not happening through ssms

Author
Message
dastagiri16
dastagiri16
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 558
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)

Group: General Forum Members
Points: 195507 Visits: 23766
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dastagiri16
dastagiri16
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 558
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.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)

Group: General Forum Members
Points: 797465 Visits: 45952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dastagiri16
dastagiri16
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 558
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.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)

Group: General Forum Members
Points: 845614 Visits: 48504
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


dastagiri16
dastagiri16
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 558
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'

GilaMonster
GilaMonster
SSC Guru
SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)SSC Guru (845K reputation)

Group: General Forum Members
Points: 845614 Visits: 48504
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)

Group: General Forum Members
Points: 797465 Visits: 45952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)SSC Guru (797K reputation)

Group: General Forum Members
Points: 797465 Visits: 45952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search