Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update trigger for different tables

Author  Topic 

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 14:02:42
Hi,

I'm trying to have an update trigger for two tables.
My tables are almost identical.

I have two tables ClientContact and ClientInformation. ClientInformation contains the exact columns as in ClientContact but it has one extra column(LocalID). It's set as NOT null and primary key.

What I want is to update column ClientID in ClientInformation everytime is updated in ClientContact.

I wrote this trigger:

USE [TRI_Billing]
GO
/****** Object:  Trigger [dbo].[updateClientID]    Script Date: 07/05/2012 08:36:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[updateClientID] ON [dbo].[ClientContact]

FOR	 UPDATE 

AS 
if UPDATE (ClientID)
BEGIN
	
	declare @clientID varchar (15);
	declare @oldClientID varchar (15);
	
	set @clientID=(select ClientID from inserted )
	set @oldClientID=(select ClientID from deleted)
	
	if @clientID !=@oldClientID
	
	begin	
	insert into ClientInformation (ClientID) values (@clientID);
	
END end


When I edit ClientID in ClientContact and try to save I get the error:

quote:
Cannot insert the value Null into column 'LocalID'


How to by pass that, since I don't have LocalID column in my ClientContact?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-07-05 : 14:08:19
This is really not a good use of triggers. But, in order to solve the issue you posted about I can think of two options:
1. Set a default LocalID (like unknown).
2. Change the nullability of LocalID.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-07-05 : 14:14:23
>> I'm trying to have an update trigger for two tables.

From what you posted, that would be false

In any event, keep triggers simple and apply one for each table

Not sure what you want to do here

Or what value it would be

Where do you get client id? do you use a single sign on, or manage users at the db level?
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 14:29:40
quote:
Originally posted by Lamprey

This is really not a good use of triggers. But, in order to solve the issue you posted about I can think of two options:
1. Set a default LocalID (like unknown).
2. Change the nullability of LocalID.


I can't change the nullability of LocalID because it's a primary key.

X002548,

All I want to do is update column ClientID in ClientInformation table, every time ClientID in ClientContact is updated.

I've tried this as well.

USE [TRI_Billing]
go
ALTER trigger [dbo].[updateClientID] ON [dbo].[ClientContact]

FOR	 UPDATE 

AS 
if UPDATE (ClientID)
BEGIN
	
	declare @clientID varchar (15);
		
	insert into ClientInformation (ClientID) values (@clientID);
	
END


I just don't understand why it's inserting everything. Shouldn't be just inserting the value of ClientID ONLY?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 14:45:40
you're telling update value in ClientInformation but you're actually doing an insert. Anyways, if you want to update, on what basis you want it to be done? are there some other columns using which you look for a record in Clientinformation and do the update? or is it that you just want the new ClientID values to be inserted to ClientInformation table once?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 14:55:48
visakh16,

I saw that. But couldn't find anything that would actually do update for an update, rather update and insert as I'm using.

Yes, I just want the column ClientID in ClientInformation table to be updated. Nothing else. So for example:

ClientID in ClientContact = 10; now in ClientID in ClientInformation I have 10 as well, which a stored procedure does.
I want to change ClientID in ClientContact to 11. But in ClientInformation it's still be 10.

The trigger I'm writing should replace 10 to 11 in ClientInformation table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 15:20:22
but then you need to have some other field based on which you detect change of value from 10 to 11 (may be primary key) in ClientContact. Which is that field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-07-05 : 15:34:37
Maybe you should post your DDL so we can see what the keys are and how the tables relate. Then post some DML and expected results so we can run queries against real tables and data. Here are some links that can help you prepare that informaiton:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 15:45:43
quote:
Originally posted by Lamprey

Maybe you should post your DDL so we can see what the keys are and how the tables relate. Then post some DML and expected results so we can run queries against real tables and data. Here are some links that can help you prepare that informaiton:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx




This is ClientContact:

USE [TRI_Billing]
GO

/****** Object:  Table [dbo].[ClientContact]    Script Date: 07/05/2012 12:40:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ClientContact](
	[BusinessName] [varchar](120) NOT NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](30) NULL,
	[MiddleName] [varchar](30) NULL,
	[FullName] [varchar](200) NULL,
	[Address1] [varchar](150) NOT NULL,
	[Address2] [varchar](80) NULL,
	[CountryCode] [char](3) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[StateMunicipality] [varchar](80) NOT NULL,
	[PostalCode] [varchar](10) NOT NULL,
	[PrimaryPhone] [varchar](15) NOT NULL,
	[PrimaryPhoneExt] [varchar](10) NULL,
	[AlternatePhone] [varchar](15) NULL,
	[AlternatePhoneExt] [varchar](10) NULL,
	[Fax] [varchar](15) NULL,
	[PrimaryEmail] [varchar](100) NOT NULL,
	[AlternateEmail] [varchar](100) NULL,
	[CreateDate] [datetime] NOT NULL,
	[ModifyDate] [datetime] NULL,
	[ActiveDate] [datetime] NOT NULL,
	[ExpiredDate] [datetime] NULL,
	[CreatedBy] [varchar](50) NOT NULL,
	[ModifiedBy] [varchar](50) NULL,
	[ActivatedBy] [varchar](50) NOT NULL,
	[ExpiredBy] [varchar](50) NULL,
	[DBID] [int] IDENTITY(1,1) NOT NULL,
	[ClientID] [varchar](15) NULL,
PRIMARY KEY CLUSTERED 
(
	[DBID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO




This is ClientInformation:
USE [TRI_Billing]
GO

/****** Object:  Table [dbo].[ClientInformation]    Script Date: 07/05/2012 12:40:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ClientInformation](
	[TRIClientID] [int] NOT NULL,
	[ClientID] [varchar](15) NULL,
	[ExternalClientID] [int] NULL,
	[ParentClientID] [int] NULL,
	[PaymentTerms] [char](21) NULL,
	[IsThereFlatRate] [bit] NULL,
	[FlatRate] [money] NULL,
	[IsThereRatePlan] [bit] NULL,
	[RatePlanID] [int] NULL,
	[CreateDate] [datetime] NOT NULL,
	[ModifyDate] [datetime] NULL,
	[ActiveDate] [datetime] NOT NULL,
	[ExpiredDate] [datetime] NULL,
	[CreatedBy] [varchar](50) NOT NULL,
	[ModifiedBy] [varchar](50) NULL,
	[ActivatedBy] [varchar](50) NOT NULL,
	[ExpiredBy] [varchar](50) NULL,
	[BillToClientID] [int] NULL,
	[MonthlyFee] [money] NULL,
	[MonthlyMin] [money] NULL,
	[SetUpFee] [money] NULL,
	[PaidSetUp] [bit] NULL,
	[VolumePricing] [bit] NULL,
	[ExactPricing] [bit] NULL,
	[Currency] [varchar](3) NULL,
	[BillingCycleDay] [int] NULL,
	[OutBoundCharage] [money] NULL,
	[OutBoundCharageInt] [money] NULL,
	[AccountType] [varchar](1) NULL,
	[VRIRate] [money] NULL,
	[DBID] [int] NULL,
 CONSTRAINT [PK_ClientInformation] PRIMARY KEY CLUSTERED 
(
	[TRIClientID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ClientInformation]  WITH CHECK ADD  CONSTRAINT [database_id] FOREIGN KEY([DBID])
REFERENCES [dbo].[ClientContact] ([DBID])
GO

ALTER TABLE [dbo].[ClientInformation] CHECK CONSTRAINT [database_id]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_MonthlyMin]  DEFAULT ((0)) FOR [MonthlyMin]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_SetUpFee]  DEFAULT ((0)) FOR [SetUpFee]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_ExactPricing]  DEFAULT ((0)) FOR [ExactPricing]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_Currency]  DEFAULT ('USD') FOR [Currency]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_BillingCycleDay]  DEFAULT ((1)) FOR [BillingCycleDay]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_OutBoundCharage]  DEFAULT ((0)) FOR [OutBoundCharage]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_OutBoundCharageInt]  DEFAULT ((0)) FOR [OutBoundCharageInt]
GO

ALTER TABLE [dbo].[ClientInformation] ADD  CONSTRAINT [DF_ClientInformation_VRIRate]  DEFAULT ((0)) FOR [VRIRate]
GO




Sample output:

ClientContact table:
ClientID column: TEST1234

ClientInformation table:
ClientID column: TEST1234

When in ClientContact table, ClientID column changes to TEST1111

trigger should update ClientID column in Clientinformation from TEST1234 to TEST1111
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 15:53:40
[code]
USE [TRI_Billing]
go
ALTER trigger [dbo].[updateClientID] ON [dbo].[ClientContact]

FOR UPDATE

AS
if UPDATE (ClientID)
BEGIN

UPDATE ci
SET ci.ClientID = i.ClientID
FROM ClientInformation ci
INNER JOIN INSERTED i
ON i.DBID = ci.database_id
INNER JOIN DELETED d
ON d.DBID = i.DBID
WHERE d.ClientID <> i.ClientID

END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 16:02:22
Shouldn't this line

ON i.DBID = ci.database_id


be:

ON i.DBID = ci.DBID
?

Because when I compile with ci.database_id I get invalid column name database_id.

When I compile with ci.DBID it changes and let me save that change. However, it updates in ClientContact, but in ClientInformation it does not get updated.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-07-05 : 16:05:51
Even better would be to remove the ClientID from one of the tables (ClientInformation?) as it is redundant. I'd also talk to your manager about doing a schema review to see if there are other poorly designed schema elements like this to be fixed.
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 16:10:12
I know. I did not wrote this tables. That's why I have to work my way around it.

What happens is that one ClientID can contain many TRI_ClientID. Thus, I cannot remove anything.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 16:13:59
quote:
Originally posted by burzum

Shouldn't this line

ON i.DBID = ci.database_id


be:

ON i.DBID = ci.DBID
?

Because when I compile with ci.database_id I get invalid column name database_id.

When I compile with ci.DBID it changes and let me save that change. However, it updates in ClientContact, but in ClientInformation it does not get updated.

ah ok it should be DBID misread name from constraint
hmm...were you updating it from NULL to some value? if yes, make a slight amendment


USE [TRI_Billing]
go
ALTER trigger [dbo].[updateClientID] ON [dbo].[ClientContact]

FOR	 UPDATE 

AS 
if UPDATE (ClientID)
BEGIN
	
	UPDATE ci
        SET ci.ClientID = i.ClientID 
        FROM ClientInformation ci
        INNER JOIN INSERTED i
        ON i.DBID = ci.database_id
        INNER JOIN DELETED d
        ON d.DBID = i.DBID
        WHERE COALESCE(d.ClientID,'') <> COALESCE(i.ClientID ,'')
	
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 16:20:04
No, there's no NULL values in ClientID column in ClientContact table.

If ClientID in ClientContact table is TEST1234

I change it to TEST1111, it changes in ClientContact, however ClientInformation is still not getting updated after trigger executes.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 16:30:39
and you're sure that you had a record for same DBID in ClientInformation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-05 : 16:43:18
Good point. That resolves the problem.

Thank you. You've been helpful as always.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 16:45:00
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-07-06 : 11:09:21
quote:
Originally posted by burzum

Good point. That resolves the problem.

Thank you. You've been helpful as always.


Perhaps...

Fundamentally, no
Go to Top of Page

burzum
Yak Posting Veteran

55 Posts
Posted - 2012-07-06 : 12:14:37
X002548,

Yes, if we dig in into this, it's not the right way, however it fixed the problem I was facing.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-07-06 : 12:55:51
What if someone updates the ClientID in the ClientInformation table? How do you enforce that the ClientID value remain accurate/correct/synced with the one in the ClientContact table?
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site