From: Peter Newman on
SQL2008

I have two databases, both with identical tables ( apart from TableNames ).
I have tried putting an update trigger on DB1.tbCBMOAccount to update the
field country on DB2.tbBOSS_OAccount with the updated value from
DB1.tbCBMOAccount

ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
ON [DB1].[dbo].[tbCBMOAccount]
AFTER UPDATE
AS
BEGIN

Declare @OAccountID UniqueIdentifier
Declare @Country varchar(32)
Select @OAccountID = OAccountID , @Country = Country From Inserted

IF UPDATE(Country)
BEGIN
UPDATE DB2.dbo.tbBOSS_OAccount
SET Country = @Country
WHERE OAccountID = @OAccountID
END
END

I have double checked, there is only 1 record in each table with the same
UniqueIdentifier. When I update country DB1.tbCBMOAccount, i get the
following error;

No Row was updated

The data in row 4 was not committed
Error Message: The row value(s) updated or deleted either do not make the
row unique or they alter multiple rows(2)
From: Dan on

"Peter Newman" <PeterNewman(a)discussions.microsoft.com> wrote in message
news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE(a)microsoft.com...
> SQL2008
>
> I have two databases, both with identical tables ( apart from
> TableNames ).
> I have tried putting an update trigger on DB1.tbCBMOAccount to update the
> field country on DB2.tbBOSS_OAccount with the updated value from
> DB1.tbCBMOAccount
>
> ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> ON [DB1].[dbo].[tbCBMOAccount]
> AFTER UPDATE
> AS
> BEGIN
>
> Declare @OAccountID UniqueIdentifier
> Declare @Country varchar(32)
> Select @OAccountID = OAccountID , @Country = Country From Inserted
>
> IF UPDATE(Country)
> BEGIN
> UPDATE DB2.dbo.tbBOSS_OAccount
> SET Country = @Country
> WHERE OAccountID = @OAccountID
> END
> END
>
> I have double checked, there is only 1 record in each table with the same
> UniqueIdentifier. When I update country DB1.tbCBMOAccount, i get the
> following error;
>
> No Row was updated
>
> The data in row 4 was not committed
> Error Message: The row value(s) updated or deleted either do not make the
> row unique or they alter multiple rows(2)


Don't write triggers that only handle single row updates. I think this might
work:


ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
ON [DB1].[dbo].[tbCBMOAccount]
AFTER UPDATE
AS
BEGIN

IF UPDATE(Country)
BEGIN
UPDATE a
SET Country = b.Country
FROM DB2.dbo.tbBOSS_OAccount a
INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
END
END



--
Dan




From: John Bell on
On Wed, 19 May 2010 05:43:01 -0700, Peter Newman
<PeterNewman(a)discussions.microsoft.com> wrote:

>SQL2008
>
>I have two databases, both with identical tables ( apart from TableNames ).
>I have tried putting an update trigger on DB1.tbCBMOAccount to update the
>field country on DB2.tbBOSS_OAccount with the updated value from
>DB1.tbCBMOAccount
>
>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> ON [DB1].[dbo].[tbCBMOAccount]
> AFTER UPDATE
>AS
>BEGIN
>
>Declare @OAccountID UniqueIdentifier
>Declare @Country varchar(32)
>Select @OAccountID = OAccountID , @Country = Country From Inserted
>
>IF UPDATE(Country)
> BEGIN
> UPDATE DB2.dbo.tbBOSS_OAccount
> SET Country = @Country
> WHERE OAccountID = @OAccountID
> END
>END
>
>I have double checked, there is only 1 record in each table with the same
>UniqueIdentifier. When I update country DB1.tbCBMOAccount, i get the
>following error;
>
>No Row was updated
>
>The data in row 4 was not committed
>Error Message: The row value(s) updated or deleted either do not make the
>row unique or they alter multiple rows(2)

Hi

Although there is only 1 row in the table now, I would always
recommend that a trigger is written in a way that would not fail if
there was multiple rows in the table.

ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
ON [DB1].[dbo].[tbCBMOAccount]
AFTER UPDATE
AS
BEGIN

IF UPDATE(Country)
BEGIN
UPDATE t2
SET Country = i.Country
FROM DB2.dbo.tbBOSS_OAccount
JOIN inserted i ON i.OAccountID = t2.OAccountID
AND t2.Country <> i.Country

END

END

You can run SQL profiler at statement level to see which if the
trigger gets executed.

Please post DDL and example data as SQL statements if you still have
problems.

John
From: John Bell on
On Wed, 19 May 2010 14:37:02 +0100, John Bell
<jbellnewsposts(a)hotmail.com> wrote:

>On Wed, 19 May 2010 05:43:01 -0700, Peter Newman
><PeterNewman(a)discussions.microsoft.com> wrote:
>
>>SQL2008
>>
>>I have two databases, both with identical tables ( apart from TableNames ).
>>I have tried putting an update trigger on DB1.tbCBMOAccount to update the
>>field country on DB2.tbBOSS_OAccount with the updated value from
>>DB1.tbCBMOAccount
>>
>>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>> ON [DB1].[dbo].[tbCBMOAccount]
>> AFTER UPDATE
>>AS
>>BEGIN
>>
>>Declare @OAccountID UniqueIdentifier
>>Declare @Country varchar(32)
>>Select @OAccountID = OAccountID , @Country = Country From Inserted
>>
>>IF UPDATE(Country)
>> BEGIN
>> UPDATE DB2.dbo.tbBOSS_OAccount
>> SET Country = @Country
>> WHERE OAccountID = @OAccountID
>> END
>>END
>>
>>I have double checked, there is only 1 record in each table with the same
>>UniqueIdentifier. When I update country DB1.tbCBMOAccount, i get the
>>following error;
>>
>>No Row was updated
>>
>>The data in row 4 was not committed
>>Error Message: The row value(s) updated or deleted either do not make the
>>row unique or they alter multiple rows(2)
>
>Hi
>
>Although there is only 1 row in the table now, I would always
>recommend that a trigger is written in a way that would not fail if
>there was multiple rows in the table.
>
>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> ON [DB1].[dbo].[tbCBMOAccount]
> AFTER UPDATE
>AS
>BEGIN
>
>IF UPDATE(Country)
> BEGIN
> UPDATE t2
> SET Country = i.Country
> FROM DB2.dbo.tbBOSS_OAccount
> JOIN inserted i ON i.OAccountID = t2.OAccountID
> AND t2.Country <> i.Country
>
>END
>
>END
>
>You can run SQL profiler at statement level to see which if the
>trigger gets executed.
>
>Please post DDL and example data as SQL statements if you still have
>problems.
>
>John


I missed the t2 alias out of the update statement!

John
From: Peter Newman on
Hi Dan,

Thanks for that, I have tried your suggestion but still it fails with same
error but this time rows = 11.

I read your comment about not using triggers for single row updates, but
why?.
The scenerio i have is that our company has two DataBases, One used buy the
shopfloor staff, and one by the management. This has been set like this for
years. If a member of staff changes any details on the shopfloor db, they
need to be reflected realtime to the management DB. I'm open to any other
suggestions of how to do this


"Dan" wrote:

>
> "Peter Newman" <PeterNewman(a)discussions.microsoft.com> wrote in message
> news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE(a)microsoft.com...
> > SQL2008
> >
> > I have two databases, both with identical tables ( apart from
> > TableNames ).
> > I have tried putting an update trigger on DB1.tbCBMOAccount to update the
> > field country on DB2.tbBOSS_OAccount with the updated value from
> > DB1.tbCBMOAccount
> >
> > ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> > ON [DB1].[dbo].[tbCBMOAccount]
> > AFTER UPDATE
> > AS
> > BEGIN
> >
> > Declare @OAccountID UniqueIdentifier
> > Declare @Country varchar(32)
> > Select @OAccountID = OAccountID , @Country = Country From Inserted
> >
> > IF UPDATE(Country)
> > BEGIN
> > UPDATE DB2.dbo.tbBOSS_OAccount
> > SET Country = @Country
> > WHERE OAccountID = @OAccountID
> > END
> > END
> >
> > I have double checked, there is only 1 record in each table with the same
> > UniqueIdentifier. When I update country DB1.tbCBMOAccount, i get the
> > following error;
> >
> > No Row was updated
> >
> > The data in row 4 was not committed
> > Error Message: The row value(s) updated or deleted either do not make the
> > row unique or they alter multiple rows(2)
>
>
> Don't write triggers that only handle single row updates. I think this might
> work:
>
>
> ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> ON [DB1].[dbo].[tbCBMOAccount]
> AFTER UPDATE
> AS
> BEGIN
>
> IF UPDATE(Country)
> BEGIN
> UPDATE a
> SET Country = b.Country
> FROM DB2.dbo.tbBOSS_OAccount a
> INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
> END
> END
>
>
>
> --
> Dan
>
>
>
>