March 3, 2018 at 6:23 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?
March 3, 2018 at 6:26 am
dastagiri16 - Saturday, March 3, 2018 6:22 AMhi,
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 3, 2018 at 6:40 am
Phil Parkin - Saturday, March 3, 2018 6:26 AMdastagiri16 - Saturday, March 3, 2018 6:22 AMhi,
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.
March 3, 2018 at 8:13 am
dastagiri16 - Saturday, March 3, 2018 6:40 AMPhil Parkin - Saturday, March 3, 2018 6:26 AMdastagiri16 - Saturday, March 3, 2018 6:22 AMhi,
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
Change is inevitable... Change for the better is not.
March 3, 2018 at 8:43 am
Jeff Moden - Saturday, March 3, 2018 8:13 AMdastagiri16 - Saturday, March 3, 2018 6:40 AMPhil Parkin - Saturday, March 3, 2018 6:26 AMdastagiri16 - Saturday, March 3, 2018 6:22 AMhi,
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.
March 3, 2018 at 9:05 am
dastagiri16 - Saturday, March 3, 2018 8:43 AMis 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
March 3, 2018 at 9:33 pm
GilaMonster - Saturday, March 3, 2018 9:05 AMdastagiri16 - Saturday, March 3, 2018 8:43 AMis 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'
March 4, 2018 at 5:17 am
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
March 4, 2018 at 12:03 pm
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
Change is inevitable... Change for the better is not.
March 4, 2018 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2018 at 6:26 pm
Phil Parkin - Saturday, March 3, 2018 6:26 AMdastagiri16 - Saturday, March 3, 2018 6:22 AMhi,
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
March 4, 2018 at 9:12 pm
Jeff Moden - Sunday, March 4, 2018 12:09 PMp.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.
March 5, 2018 at 8:36 pm
dastagiri16 - Sunday, March 4, 2018 9:12 PMJeff Moden - Sunday, March 4, 2018 12:09 PMp.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
Change is inevitable... Change for the better is not.
March 18, 2018 at 4:15 am
Jeff Moden - Monday, March 5, 2018 8:36 PMdastagiri16 - Sunday, March 4, 2018 9:12 PMJeff Moden - Sunday, March 4, 2018 12:09 PMp.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