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 14:50
Hi John,

I'm going to stand in the silly corner. Your update trigger works fine. The
error i was getting ( and still get ) is if i am in SQL Manager and editing
the table that way. Lesson learnt, Thanks for the help

Pete

"John Bell" wrote:

> 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
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Constraint Question .. 1:N Relationship with a "Primary" Attribute
Next: Filtering and adding rows based on some condition