August 20, 2009 at 11:25 am
I have a table
Table1 having Column T1ID,T1desc
Table2 having Column T2ID,T2desc
User who will update T1Desc samething needs to get update on T2Desc when T1ID=T2ID
We don't want replication as there are only 300 rows in each table.
I tried trigger but it not working for me.
Can somebody guide me asap.
August 20, 2009 at 11:29 am
Please note both tables are 2 different databases
August 20, 2009 at 11:30 am
Why have two tables with the same data, even in two different databases?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2009 at 12:05 pm
both table have different data ( ~200 rows are different) but some rows are same ( ~100 rows are same) & both dbs are use for 2 different apps.
August 20, 2009 at 1:15 pm
Any way to consolidate the two?
If not, I'd need to see the code for the tables (create scripts), and the code for the trigger. With that, I might be able to advise you on how to make it work better.
But you really should look at possibilities of consolidating the data. Perhaps one table with a column that indicates which application it's for. It's much easier to manage that way, and doesn't require triggers or other synchronization tools, so it's more robust too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2009 at 1:45 pm
USE DATA1 --DBNAME
GO
CREATE TABLE [dbo].[TABLE1](
[ID1] [int] NULL,
[Desc1] [varchar](50) NULL
) ON [PRIMARY]
USE DATA2 --DB NAME
GO
CREATE TABLE [dbo].[TABLE2](
[ID1] [int] NULL,
[Desc2] [varchar](50) NULL
) ON [PRIMARY]
GO
Here is trigger
CREATE TRIGGER [dbo].[TrigTest]
On [dbo].[T1]
FOR UPDATE
AS
BEGIN
UPDATE DATA1.dbo.T2
SET Desc2 = Desc1
where ID2 IN ( SELECT ID1 FROM DATA1.dbo.T1)
END
August 20, 2009 at 2:24 pm
Your update statement isn't written correctly.
Try this:
create trigger dbo.TrigTest
on dbo.Table1
for update
as
update t2
set desc2 = desc1
from inserted
inner join data2.dbo.table2 t2
on inserted.id1 = t2.id1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2009 at 2:37 pm
sweet
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply