From: Dan on

"Peter Newman" <PeterNewman(a)discussions.microsoft.com> wrote in message
news:A5C820F3-4F16-434E-BE49-680A41C4B2A8(a)microsoft.com...
> Hi Dan,
>
> Thanks for that, I have tried your suggestion but still it fails with same
> error but this time rows = 11.

Have you got any other triggers on the table being updated?


> I read your comment about not using triggers for single row updates, but
> why?.

Because an update can affect more than one row - so for instance if you had

UPDATE [tbCBMOAccount] SET Country = 'UK' WHERE Country = 'GB'

and there were 15 rows in tbCBMOAccount with the Country column set to GB,
then the update trigger would have 15 rows in the inserted and deleted
tables and your code assigns columns to variables which will fail with more
than one row.

> 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

Personally, I use transactional replication for this sort of thing - if one
database is designed to be a duplicate of the other, use replication and let
the underlying distribution system deal with it. It might however be
overkill depending on how small or large your database is.

Dan

>
> "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
>>
>>
>>
>>



From: Peter Newman on
Hi John,

Still struggling here. Code below.

CREATE TABLE [tbCBMOAccount](
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[OAccountClientID] [int] NOT NULL,
[Country] [varchar](32) NULL,
[BACSID] [varchar](6) NOT NULL,
[SortCode] [varchar](6) NOT NULL,
[AccountNumber] [varchar](8) NOT NULL,
[AccountName] [varchar](32) NOT NULL,
[DailyLimit] [money] NULL,
[WeeklyLimit] [money] NULL,
[VarPeriodDays] [int] NULL,
[VarPeriodLimit] [money] NULL,
[MonthlyLimit] [money] NULL,
[AllowPayments] [bit] NULL,
[AllowDebits] [bit] NULL,
[Active] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [tbBOSS_OAccount](
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[OAccountClientID] [int] NOT NULL,
[Country] [varchar](32) NULL,
[BACSID] [varchar](6) NOT NULL,
[SortCode] [varchar](6) NOT NULL,
[AccountNumber] [varchar](8) NOT NULL,
[AccountName] [varchar](32) NOT NULL,
[DailyLimit] [money] NULL,
[WeeklyLimit] [money] NULL,
[VarPeriodDays] [int] NULL,
[VarPeriodLimit] [money] NULL,
[MonthlyLimit] [money] NULL,
[AllowPayments] [bit] NULL,
[AllowDebits] [bit] NULL,
[Active] [bit] NULL
) ON [PRIMARY]


INSERT INTO [tbBOSS_OAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES

(111111,33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62,2,UK,111111,222222,12345678,TEST
COMPANY,1.0000,11.0000,111,23.0000,11111.0000,True,False,True)
INSERT INTO [tbBOSS_OAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES

(222222,dc408d70-977b-4354-a6ec-9bec182e043f,1,UK,430639,832845,30989312,TEST
COMPANY 2,0.0000,0.0000,5,0.0000,10000.0000,True,True,True)
INSERT INTO [tbCBMOAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES

(111111,33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62,2,GER,111111,222222,12345678,TEST COMPANY,1.0000,11.0000,111,23.0000,11111.0000,True,False,True)
INSERT INTO [tbCBMOAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES

(222222,dc408d70-977b-4354-a6ec-9bec182e043f,1,UK,430639,832845,30989312,TEST
COMPANY 2,0.0000,0.0000,5,0.0000,10000.0000,True,True,True)



"John Bell" wrote:

> 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,

I thought of transactional replication but like you said, I think this
will be a bit of overkill, pluse could result in problems if I need to update
different fields from DB2 to DB1 . Thanks anyway for your help

"Dan" wrote:

>
> "Peter Newman" <PeterNewman(a)discussions.microsoft.com> wrote in message
> news:A5C820F3-4F16-434E-BE49-680A41C4B2A8(a)microsoft.com...
> > Hi Dan,
> >
> > Thanks for that, I have tried your suggestion but still it fails with same
> > error but this time rows = 11.
>
> Have you got any other triggers on the table being updated?
>
>
> > I read your comment about not using triggers for single row updates, but
> > why?.
>
> Because an update can affect more than one row - so for instance if you had
>
> UPDATE [tbCBMOAccount] SET Country = 'UK' WHERE Country = 'GB'
>
> and there were 15 rows in tbCBMOAccount with the Country column set to GB,
> then the update trigger would have 15 rows in the inserted and deleted
> tables and your code assigns columns to variables which will fail with more
> than one row.
>
> > 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
>
> Personally, I use transactional replication for this sort of thing - if one
> database is designed to be a duplicate of the other, use replication and let
> the underlying distribution system deal with it. It might however be
> overkill depending on how small or large your database is.
>
> Dan
>
> >
> > "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
> >>
> >>
> >>
> >>
>
>
>
> .
>
From: John Bell on
On Wed, 19 May 2010 07:05:01 -0700, Peter Newman
<PeterNewman(a)discussions.microsoft.com> wrote:

>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
>>
>>
Peter

Just because it has been that way for years it doesn't mean to say it
is correct or good practice to do it. This sounds like a typical
problem where the data has changed and the code doesn't work because
it has made such an assumption.

Normally I would expect the rowcounts returned to be like:

(0 row(s) affected)

(1 row(s) affected)

Which is not what you are indicating, therefore I guess something is
happening that you are not telling us about. See what you get in SQL
Profiler as I suggested in my other post.

If the transaction rolled back then the update to tbCBMOAccount would
not be changed and if they are out of sync then the logic would be
incorrect. You don't say what the data is after this update. Scripting
out the tables and data would remove some of the ambiguites and give
us far more information.

A wild guess would be that in the trigger there is a SET NOCOUNT ON
which is suppressing the result set and the application can not cope.

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

I've updated the SQL so the inserts work!

CREATE TABLE [tbCBMOAccount](
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[OAccountClientID] [int] NOT NULL,
[Country] [varchar](32) NULL,
[BACSID] [varchar](6) NOT NULL,
[SortCode] [varchar](6) NOT NULL,
[AccountNumber] [varchar](8) NOT NULL,
[AccountName] [varchar](32) NOT NULL,
[DailyLimit] [money] NULL,
[WeeklyLimit] [money] NULL,
[VarPeriodDays] [int] NULL,
[VarPeriodLimit] [money] NULL,
[MonthlyLimit] [money] NULL,
[AllowPayments] [bit] NULL,
[AllowDebits] [bit] NULL,
[Active] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [tbBOSS_OAccount](
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[OAccountClientID] [int] NOT NULL,
[Country] [varchar](32) NULL,
[BACSID] [varchar](6) NOT NULL,
[SortCode] [varchar](6) NOT NULL,
[AccountNumber] [varchar](8) NOT NULL,
[AccountName] [varchar](32) NOT NULL,
[DailyLimit] [money] NULL,
[WeeklyLimit] [money] NULL,
[VarPeriodDays] [int] NULL,
[VarPeriodLimit] [money] NULL,
[MonthlyLimit] [money] NULL,
[AllowPayments] [bit] NULL,
[AllowDebits] [bit] NULL,
[Active] [bit] NULL
) ON [PRIMARY]


INSERT INTO [tbBOSS_OAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES
(111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'UK',
111111,222222,12345678,'TEST COMPANY',
1.0000,11.0000,111,23.0000,11111.0000,
'True','False','True');

INSERT INTO [tbBOSS_OAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES
(222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
430639,832845,30989312,'TEST COMPANY',
20.0000,0.0000,5,0.0000,10000.0000,
'True','True','True');

INSERT INTO [tbCBMOAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES
(111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'GER'
,111111,222222,12345678,'TEST COMPANY',
1.0000,11.0000,111,23.0000,11111.0000,
'True','False','True');

INSERT INTO [tbCBMOAccount]
([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID]
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit]
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments]
,[AllowDebits] ,[Active])
VALUES
(222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
430639,832845,30989312,'TEST COMPANY',
20.0000,0.0000,5,0.0000,10000.0000,
'True','True','True');

SELECT * FROM [tbCBMOAccount]

SELECT * FROM [tbBOSS_OAccount]


CREATE TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
ON [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 dbo.tbBOSS_OAccount
SET Country = @Country
WHERE OAccountID = @OAccountID
END
END

SELECT * FROM [tbCBMOAccount]

SELECT * FROM [tbBOSS_OAccount]

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GDR'
WHERE Country = 'GER'


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

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

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GDR'
WHERE Country = 'GER'

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GER'
WHERE Country = 'GDR'

I don't get any errors with this, so I assume something is different
in your environment.

John