pwalter83 (8/16/2013)
I wish to track the changes in table1 if the row is updated on table2 based on the condition that both tables are linked through primary-foreign key (VENDOR_ID). In other words, if VENDOR_NAME is changed on table2 then that change should be reflected on table1 through VENDOR_ID. Can this be achieved through a trigger ? If yes, then could you please guide me how can that be accomplished.
The DDL for table 1 and 2 are as below:
CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] [varchar](100) NOT NULL
CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL
Thanks in advance for your help.
You are close here but not quite. What you want to do is have the VendorID in table1 NOT the VendorName. That way when the name of the vendor changes you don't have to do anything in any other table.
Using you ddl as a starting point, I changed Vendor_ID in table1 to be an int. It will hold the primary key of table2 instead of the value.
Here is a full example to demonstrate.
CREATE TABLE [dbo].[table1](
[SYSTEM_ID] [int] IDENTITY(1,1) NOT NULL,
[SYSTEM_NAME] [varchar](100) NOT NULL,
[VENDOR_ID] int NOT NULL
)
CREATE TABLE [dbo].[table2](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDOR_NAME] [varchar](100) NOT NULL
)
insert table2
select 'My Vendor'
insert table1
select 'My System', SCOPE_IDENTITY()
select *
from table1 t
join table2 t2 on t.VENDOR_ID = t2.VENDOR_ID
update table2
set VENDOR_NAME = 'Name is changed'
where VENDOR_ID = 1
select *
from table1 t
join table2 t2 on t.VENDOR_ID = t2.VENDOR_ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/