August 6, 2012 at 2:54 am
Hi All,
I just setup the replication in my environment.
Table structure.
CREATE TABLE [dbo].[pwallet_tran](
[TId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[uid] [varchar](100) NOT NULL,
[currencycode] [varchar](10) NOT NULL,
[ledgerBalance] [decimal](20, 2) NULL,
[availableBalance] [decimal](20, 2) NULL,
[lastUpdateDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[TId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE TABLE [dbo].[pwallet](
[wId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[uid] [varchar](100) NOT NULL,
[currencycode] [varchar](10) NOT NULL,
[ledgerBalance] [decimal](20, 2) NULL,
[availableBalance] [decimal](20, 2) NULL,
[lastUpdateDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[wId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Trigger is used for inserting the data in pwallet_tran
ALTER TRIGGER [dbo].[trigger_wallet_history] ON [dbo].[pwallet]
FOR UPDATE
AS
INSERT INTO pwallet ([uid],[currencycode],[ledgerBalance],[availableBalance],[lastUpdateDate])
SELECT a.[uid], a.[currencycode], a.[ledgerBalance]-b.[ledgerBalance], a.[availableBalance]-b.[availableBalance], CURRENT_TIMESTAMP
FROM inserted a INNER JOIN deleted b ON a.[uid]=b.[uid];
INSERT INTO pwallet_tran ([uid],[currencycode],[ledgerBalance],[availableBalance],[lastUpdateDate])
SELECT b.[uid], b.[currencycode], b.[ledgerBalance], b.[availableBalance], CURRENT_TIMESTAMP
FROM inserted a INNER JOIN deleted b ON a.[uid]=b.[uid];
I have SQLserver 2008 R2 Enterprise edition. I setup P2P replication.
After I completed the setup I encountered the following error.
Error.
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000003BF000000D8000B00000000, Command ID: 1)
Error messages:
•Explicit value must be specified for identity column in table 'pwallet_tran' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
Get help: http://help/545
Explicit value must be specified for identity column in table 'pwallet_tran' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
Should I not use trigger to update the data for the columns used as not for replication.
Please let me know how to solve this.
Thank you very much.
August 6, 2012 at 11:46 pm
I think it is because you are trying to replicate the identity column, which is similar to insert explicit value in a identity column.
So, please check the below link may be useful..
Thanks
August 8, 2012 at 10:25 pm
Thank you very much.
July 31, 2019 at 6:34 am
I know it's been 7 years since the question was asked, but I dealt with exactly this problem yesterday. The issue is that the trigger is firing during the replication and is trying to insert into pwallet - but look at the pwallet's definition, wId is IDENTITY NOT FOR REPLICATION, which means that during replication (where your trigger is now running and failing), it does not behave as IDENTITY, which is why an insert would need to specify a value for it.
You cannot write this trigger so that it would successfully work both during normal operation and during replication, because the insert would have specify the value for the PK in one case, and NOT specify it in the other.
But do consider if you need the trigger firing during the replication - the data inserted by the trigger on the publisher would go to the subscriber anyway, due to the replication itself, so you might not need to create it again while the record is updated during replication. In that case simply mark your trigger as NOT FOR REPLICATION as well, so it won't fire.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply