Problem with Update Trigger
in [Programming SQL]
|
Prev: Constraint Question .. 1:N Relationship with a "Primary" Attribute
Next: Filtering and adding rows based on some condition
From: Peter Newman on 19 May 2010 08:43 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 19 May 2010 09:14 "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 19 May 2010 09:37 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 19 May 2010 09:57 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 19 May 2010 10:05
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 > > > > |