Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DTMarkP
Starting Member
1 Post |
 Posted - 2010-10-06 : 11:27:33
|
| I have a database with about 50 tables. I need to replicate changes selectively from this database to another, so I'm using AFTER triggers on the necessary tables.Some work and some don't. By "work" I mean that some fire perfectly as expected, and for other tables, they never fire - have verified that in the Profiler.I can't see any common connection between the tables where it is failing. As an example, here's one:--------------------------------------------------------Table definition:--------------------------------------------------------CREATE TABLE [dbo].[CustomerGroup]( [CustomerGroupID] [int] IDENTITY(1,1) NOT NULL, [CreatedDateTime] [smalldatetime] NOT NULL, [ModifiedDateTime] [smalldatetime] NULL, [CustomerGroupName] [nvarchar](75) NOT NULL, [CanOrderOnline] [bit] NOT NULL, [ShowPrices] [bit] NOT NULL, [IsBuiltIn] [bit] NOT NULL, CONSTRAINT [PK_CustomerGroup] PRIMARY KEY CLUSTERED ( [CustomerGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[CustomerGroup] ADD CONSTRAINT [DF_CustomerGroup_CanOrderOnline] DEFAULT ((1)) FOR [CanOrderOnline]GOALTER TABLE [dbo].[CustomerGroup] ADD CONSTRAINT [DF_CustomerGroup_ShowPrices] DEFAULT ((1)) FOR [ShowPrices]GOALTER TABLE [dbo].[CustomerGroup] ADD CONSTRAINT [DF_CustomerGroup_IsBuiltIn] DEFAULT ((0)) FOR [IsBuiltIn]GO--------------------------------------------------------Trigger definition:--------------------------------------------------------ALTER TRIGGER [dbo].[OnCustomerGroupInsertUpdate] ON [dbo].[CustomerGroup]AFTER INSERT,UPDATE AS SET NOCOUNT ON DECLARE @ID int SELECT @ID = CustomerGroupID FROM [inserted] IF @ID IS NOT NULL BEGIN EXEC proc_SyncTask_Insert 'customergroup', @ID, 'U' END--------------------------------------------------------This trigger won't work for multiple rows, but I haven't got as far as that yet. I just want to see it working.The triggers are coded pretty well identically on all the tables - all but for the declaration with the table name, and the name of the PK in each case.Have tried editing the rows in Grid View in Management Studio, and also tried manually updating a row with an UPDATE statement which returns the expected 1 row(s) affected and the UPDATE succeeds, but the trigger simply doesn't fire.This is going to be something obvious which I'll kick myself for later.Can anyone tell me what it is? :) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 11:53:22
|
| I'll assume you know what you're doing by implementing replication manually rather than using Sql Server's replication functionality. But that sounds fishy :)I compiled your code and the trigger is firing for me - both inserts and updates. How do you know it is "not firing"? Try temporarily adding a PRINT statement right after the "create trigger...AS" then do an UPDATE from a query window and see if your PRINT prints.And yes, your code will only do one row of a multi-row statement. You would normally not use a variable but rather JOIN to inserted by the PrimaryKey column(s). That way all inserted/updated rows get handled. Your variable will only capture one of the values. If you can perform the "proc_synctask_insert" logic on many IDs you should remove the EXEC proc call and apply the logic directly using the inserted table to all rows at once. If you have to use the proc then you will be forced to loop through the IDs in inserted and call the proc within the loop. That will be highly inefficient.Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Trigger not firing