September 7, 2011 at 10:31 am
We have a database which is push subscriber for a publisher in other environment (other group in my company)
In order to audit the dml changes in subscriber tables I have created triggers on some of the tables to track the changes and inserts into the other database in th same instance.
but the issue is they are dissapearing when they are re-initializing the all the subscribers.
How to prevent triggers from dissapearing?
September 7, 2011 at 11:38 am
You will have to change the publication (article) and stop the article behaviour of dropping the object upon reinitialisation. This is the default behaviour for all new articles.
http://msdn.microsoft.com/en-us/library/ms175980.aspx
pre_creation_cmd -Pre-creation command that can drop, delete, or truncate the destination table before synchronization is applied.
none -Does not use a command.
drop -Drops the destination table.
delete -Deletes the destination table.
truncate -Truncates the destination table.
September 7, 2011 at 11:41 am
How do you re-initialize? Snapshot or from backup?
If you initialize from backup you'll need to re-create the triggers everytime (since your audit tables are in another DB they'll not be affected).
If you re-initialize from snapshot do you drop and re-create the articles or delete/truncate them?
If you are dropping them (the @pre_creation_cmd parameter to sp_addarticle which defaults to a drop) then that would be why your triggers are vanishing.
You can either change the @pre_creation_cmd to truncate (will not help if you make schema changes on the publisher and want these to flow down) or put in a script to create the triggers whenever a snapshot is created (the @post_snapshot_script parameter to sp_addpublication or using sp_changepublication)
Also consider the performance impact of having triggers on subscriber tables (if you have something like transactional replication setup). For e.g. a 100,000 row update/insert/delete on the publisher table will translate to 100,000 commands individually applied which translates to 100,000 invocations of your trigger.
September 7, 2011 at 11:43 am
This is true but it is not possible in our environment as publisher is having multiple subscribers and different requirements.
We can make changes only on our end. Can you please suggest me any other way we can do on our end.
I do tried CDC but unabled to enable on the table , throwing error server principle is unable to access under current security context.
September 7, 2011 at 12:13 pm
winash (9/7/2011)
How do you re-initialize? Snapshot or from backup?
using snapshot
If you initialize from backup you'll need to re-create the triggers everytime (since your audit tables are in another DB they'll not be affected).
If you re-initialize from snapshot do you drop and re-create the articles or delete/truncate them?
they are re-initilising with create new snapshot
If you are dropping them (the @pre_creation_cmd parameter to sp_addarticle which defaults to a drop) then that would be why your triggers are vanishing.
You can either change the @pre_creation_cmd to truncate (will not help if you make schema changes on the publisher and want these to flow down) or put in a script to create the triggers whenever a snapshot is created (the @post_snapshot_script parameter to sp_addpublication or using sp_changepublication)
Also consider the performance impact of having triggers on subscriber tables (if you have something like transactional replication setup). For e.g. a 100,000 row update/insert/delete on the publisher table will translate to 100,000 commands individually applied which translates to 100,000 invocations of your trigger.
this database is not transactional that much and the updates and changes are not many in a day.
approx. 1000 rows in day are updateble
September 7, 2011 at 12:24 pm
You could include a post snapshot script to reapply the triggers and any depandancies.
http://technet.microsoft.com/en-us/library/ms188413.aspx
sp_changepublication
post_snapshot_script
September 7, 2011 at 12:45 pm
September 8, 2011 at 2:25 am
@SQLFRNDZ (9/7/2011)
Do I have any other way that I can re-create a job to create triggers when there is a re-initialisation using sanpshot.Intension is how would I detect the re-initialization ?
and then kick the job to re-create the triggers
Any base Idea would be help ful.
You could do it in a sql agent job.
This is some of the code of the test I did to prove the concept to you. You could embed this in a stored proc and call the proc from te jobstep to make the job a bit more managable.
declare @sqlcmd varchar(8000)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
SET @sqlcmd = 'CREATE TRIGGER [dbo].[test_trg] ON [dbo].[testtable]' + @NewLineChar
SET @sqlcmd = @sqlcmd +'FOR INSERT' + @NewLineChar
SET @sqlcmd = @sqlcmd +'AS' + @NewLineChar
SET @sqlcmd = @sqlcmd +'begin' + @NewLineChar
SET @sqlcmd = @sqlcmd +'return' + @NewLineChar
SET @sqlcmd = @sqlcmd +'end'
/****** Object: Trigger [test_trg] Script Date: 09/08/2011 09:16:31 ******/
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[test_trg]'))
exec (@sqlcmd)
and this is the job
USE [msdb]
GO
/****** Object: Job [ReCreate Triggers] Script Date: 09/08/2011 09:22:55 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/08/2011 09:22:55 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ReCreate Triggers',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Recreate insert trigger on test] Script Date: 09/08/2011 09:22:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recreate insert trigger on test',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @sqlcmd varchar(8000)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
SET @sqlcmd = ''CREATE TRIGGER [dbo].[test_trg] ON [dbo].[testtable]'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''FOR INSERT'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''AS'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''begin'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''return'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''end''
/****** Object: Trigger [test_trg] Script Date: 09/08/2011 09:16:31 ******/
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[test_trg]''))
exec (@sqlcmd)',
@database_name=N'Test',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
You could either schedule the job as it wont do anything unless the trigger is missing or you could create a ddl trigger for table creation on the database as the database (and trigger) will be unaffected by reinitialisation.
CREATE TRIGGER [db_recreatetriggers]
ON DATABASE
FOR CREATE_TABLE
begin
exec sp_start_job @job_name = 'ReCreate Triggers'
end
go
September 8, 2011 at 3:54 am
@SQLFRNDZ (9/7/2011)
We have a database which is push subscriber for a publisher in other environment (other group in my company)In order to audit the dml changes in subscriber tables I have created triggers on some of the tables to track the changes and inserts into the other database in th same instance.
but the issue is they are dissapearing when they are re-initializing the all the subscribers.
How to prevent triggers from dissapearing?
I saw you getting pretty good advices to your question. I have adopted a generic approach. When I need to generate a snapshot for re-initializing, I always choose to use 'truncate destination object' if it exists.
This way I can maintain the needs of subscriber. You need different indexes and statistics on publisher table or subscriber table due to different nature of their use. so if i use default option, i loose all indexes and statistics for any application using my subscription tables thus generating bad query plans.
Thanks
Chandan
September 8, 2011 at 12:57 pm
MysteryJimbo (9/8/2011)
@SQLFRNDZ (9/7/2011)
Do I have any other way that I can re-create a job to create triggers when there is a re-initialisation using sanpshot.Intension is how would I detect the re-initialization ?
and then kick the job to re-create the triggers
Any base Idea would be help ful.
You could do it in a sql agent job.
This is some of the code of the test I did to prove the concept to you. You could embed this in a stored proc and call the proc from te jobstep to make the job a bit more managable.
declare @sqlcmd varchar(8000)DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
SET @sqlcmd = 'CREATE TRIGGER [dbo].[test_trg] ON [dbo].[testtable]' + @NewLineChar
SET @sqlcmd = @sqlcmd +'FOR INSERT' + @NewLineChar
SET @sqlcmd = @sqlcmd +'AS' + @NewLineChar
SET @sqlcmd = @sqlcmd +'begin' + @NewLineChar
SET @sqlcmd = @sqlcmd +'return' + @NewLineChar
SET @sqlcmd = @sqlcmd +'end'
/****** Object: Trigger [test_trg] Script Date: 09/08/2011 09:16:31 ******/
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[test_trg]'))
exec (@sqlcmd)
and this is the job
USE [msdb]GO
/****** Object: Job [ReCreate Triggers] Script Date: 09/08/2011 09:22:55 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/08/2011 09:22:55 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ReCreate Triggers',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Recreate insert trigger on test] Script Date: 09/08/2011 09:22:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recreate insert trigger on test',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @sqlcmd varchar(8000)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
SET @sqlcmd = ''CREATE TRIGGER [dbo].[test_trg] ON [dbo].[testtable]'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''FOR INSERT'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''AS'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''begin'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''return'' + @NewLineChar
SET @sqlcmd = @sqlcmd +''end''
/****** Object: Trigger [test_trg] Script Date: 09/08/2011 09:16:31 ******/
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[test_trg]''))
exec (@sqlcmd)',
@database_name=N'Test',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
You could either schedule the job as it wont do anything unless the trigger is missing or you could create a ddl trigger for table creation on the database as the database (and trigger) will be unaffected by reinitialisation.
CREATE TRIGGER [db_recreatetriggers]
ON DATABASE
FOR CREATE_TABLE
begin
exec sp_start_job @job_name = 'ReCreate Triggers'
end
go
Thanks you for this post, that is what my thought too create a job to recreate the triggers but the really good thing heard from you is create a database trigger for create_table....that way I don't need to run this job manually or schedule it. I will go ahead and do it and see how that works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply