Problem with alter table with merge replication

Question

Problem with alter table with merge replication

Tarun Kapoor

Old Hand

Points: 335
More actions
November 26, 2005 at 9:42 pm

#90930

I have code similar to below in the insert Trigger of a few tables.
CREATE TRIGGER [insertTrigger] ON dbo.CDSMaster
FOR INSERT
AS
begin
declare @CDSId int
declare @CDSCode varchar(15)

select @CDSCode=(select ltrim(rtrim(lower(CDSCode))) from inserted)
select @CDSId=(Select CDSId from inserted)
alter table [dbo].[CDSMaster] disable trigger [updateTrigger]
update cdsmaster set CDSCode=@CDSCode where CDSId=@CDSId
alter table [dbo].[CDSMaster] enable trigger [updateTrigger]
end
The reason for having the above code is to change the CASE of certain columns which i can't do for some reason in the front-end that uses the table.

The problem happens when i uses i publish this table. Once i turn on the replication, inserts in the table give me an error as the "alter table " statement in the above trigger is not allowed.
Does anyone have any idea to do the same thing in a better way?

I have found a half-workaround. It lets me disable/enable the triggers if i have the following before the alter table statements.
exec sp_msunmarkreplinfo 'cdsmaster'
The above stored proc changes the value of replinfocolumn to 0. However, i do not know a way to change the value of replinfo column to its original value once i am done.
Thanks for your time!!
VB.NET Prorammer (sometimes DBA also )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

If you're on SQL Server 2000 or 2005, use an INSTEAD OF TRIGGER. For instance:

CREATE TRIGGER [insertTrigger] ON dbo.CDSMaster
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO dbo.CDSMaster
  (CDSID, CDSCode, <... rest of columns ...> )
  SELECT CDSID, LTRIM(RTRIM(LOWER(CDSCode))), <... rest of columns ...>
  FROM inserted
END

K. Brian Kelley
@kbriankelley

Tarun Kapoor Old Hand Points: 335 More actions · Answer 2

Thank You Sir!! It worked like a charm.

I remember using a "BEFORE INSERT" trigger long time back. Didn't knew they replaced it with this one.