trigger to update a value between databases

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

SQL Server 2008 Forums

Transact-SQL (2008)

trigger to update a value between databases

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2011-01-12 : 20:33:49

I am trying to write a trigger but failing miserably, what I want is a trigger to update a field in a table based on a value from another database.

So I have data in the following place

Database = DB1
Table = Table1
Field = FieldA
Field – TBID

When FieldA changes I want it to update FieldX in the following database

Database = DB2
Table = Table1
Field = FieldX
Field – TBID

Both records are linked together by the value in TBID so that is how the two will identify each other, any ideas welcomed, thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-12 : 21:10:09

[code]
Create table TableA (TBID int null, F1 int null)

Create Table TableB (TBID int null, F1 int null)

Insert into TableA
Select 1,1

Insert into TableB
Select 1,2

GO
Create trigger tuA on TableA
AFTER UPDATE
AS

Update TableB
SET F1 =inserted.F1
FROM inserted
Where TableB.TBID = inserted.TBID

GO
Select * FROM TableA
Select * FROM TableB

Update TableA
Set F1 = 3
GO
Select * FROM TableA
Select * FROM TableB
[/code]

That is the basics...you will have to test though...Triggers can be a nasty business sometimes.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:01:02

for table in other db use dbname.schemaname.tablename in queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources