Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication Issue. Inserting through a Trigger. Expand / Collapse
Author
Message
Posted Monday, August 06, 2012 2:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:27 PM
Points: 1,242, Visits: 1,097
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.
Post #1340417
Posted Monday, August 06, 2012 11:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 7:24 AM
Points: 877, Visits: 256
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..
http://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/


Thanks
Post #1341012
Posted Wednesday, August 08, 2012 10:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:27 PM
Points: 1,242, Visits: 1,097
Thank you very much.
Post #1342374
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse