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: Dan on 19 May 2010 10:29 "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 19 May 2010 11:27 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 19 May 2010 11:29 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 19 May 2010 12:13 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 19 May 2010 13:23
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 |