Trigger with two database

hi..

i have a two database like testDB and SampleDB. both DB table names are tabletest and tablesample  and field name also same for both tables.

Now i need ur help.. ie..I have .bak file in my system when i restore this .bak file in testDB at the same time all the fields are update to SampleDB tablesample  if it is possible pls guid me and send code...

Thanking u..
rengsonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
it is not possible to execute trigger while restoring the DB. however it is possible to set the trigger on tableTest and with every insert, update and delete, it affect database SampleDB's table.

you can write trigger with fully qualified name of table something like

Insert into ServerName.SampleDB.SchemaName.TableSample (valuesList)

you can execute above statement from TestDB's trigger.
0
BodestoneCommented:
Or you could script your restore and use the script to restore both databases at the same time.
For example:
RESTORE DATABASE [TestDB] FROM  DISK = N'D:\PathToBackup\TestDB_backup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

GO

RESTORE DATABASE [SampleDB] FROM  DISK = N'D:\PathToBackup\TestDB_backup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

Open in new window

0
LowfatspreadCommented:
it is not a good idea to have cross database triggers as this really complicates a backup/restore scenario.

do you actually have such a situation?

can you explain in more detail what the relationship between the databases is?
 
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rengsonsAuthor Commented:
create TRIGGER dbo.trigg
ON dbo.tabletest
FOR INSERT, UPDATE  
AS  
BEGIN  
SET NOCOUNT ON
INSERT INTO SampleDB.dbo.tabletest
SELECT id, name,age,add

   FROM Inserted where id='SSTIP'
END



This is my trigger.

0
RiteshShahCommented:
what is the issue? isn't it working?
0
rengsonsAuthor Commented:
Both DB has same tablenames and same fields..

This trigger is run successfully..
when i changed testDb table also automaticaly updated in SamleDB through query analyser. but restore via application TestDB only update no changes in SampleDb
0
RiteshShahCommented:
can you try to insert some records manually in TestDB? just wanted to check whether it works or not. as while restoring DB, your table trigger may not fire as I stated it in first statement in my first answer.
0
rengsonsAuthor Commented:
Thank u Ritesh..

s i'm tried and insert manually its work both DB's. (Trigger is fire)
but restore via application TestDB only update no changes in SampleDb

can u give ur statement briefly(example)..?
0
RiteshShahCommented:
>>but restore via application TestDB only update no changes in SampleDb<<

can you please elaborate your above statement first, please?
0
rengsonsAuthor Commented:
i'm developed in windows application using asp.net c# and sql server 2005 express.
i want to get TestDB Databse backup & restore through this application.
im gettin TestDB's .bak file its no probs. restore this .bak file  to
TestDb its also done..I want to restore .bak file to TestDB at the same time all the fields are update to SampleDB its possible..?

so only im trying to above trigger..

0
BodestoneCommented:
Did you try eunnign the script torestore both databases at the same time rather than use the appliction?
0
rengsonsAuthor Commented:
HI Bodestone,,,

am tried to below query but im gettin error..

RESTORE DATABASE TESTDB
FROM  DISK = 'D:\Backup\Test.bak'
WITH MOVE 'Test' To 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test_data.mdf ',
move 'Test_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test_log.ldf',
replace
---------------------------------------------------

Msg 3234, Level 16, State 2, Line 1
Logical file 'Test' is not part of database 'KABackup'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

0
BodestoneCommented:
If you use WITH MOVE then it would only be on the second one.
If you run:
RESTORE FILELISTONLY FROM DISK= 'D:\Backup\Test.bak'
This will tell you what the logical names actually are.
0
rengsonsAuthor Commented:
ALTER TRIGGER [dbo].[test]
ON [dbo].[TableName1]
FOR UPDATE,DeLETE
AS  
BEGIN  
SET NOCOUNT ON
update SAMPLEDB.dbo.TableName1
set
name= Ins.name,
age=Ins.age,
add=Ins.add
from inserted Ins inner join deleted del on Ins.name=del.name
where SAMPLEDB.dbo.TableName1.name=del.name
END
----------------------------------------------------------------------------
Hi in TESTDB hav 10 tables [TableName1 to TableName10]
SAMPLEDB hav a only one table [TableName1] but both TableName1 fields are same.
am i write the above trigger TableName1  in TESTDB..
This trigger fire SAMPLEDB TableName1 and update all fields no probs.
This update is run only via query analyser..

But i need whenever i restore .bak file into TESTDB at the same time SAMPLEDB TableName1 also updated..

How can i do this..?
0
BodestoneCommented:
That trigger only updates the SampleDB table when new entries are added to the TestDB table using an INSERT statement.

I don't think you can get it to trigger directly after a restore unless again you also do the restore via script:

Assuming you wanted to exactly duplicate that table then...
USE MASTER

GO

RESTORE DATABASE [TestDB] FROM  DISK = N'D:\PathToBackup\TestDB_backup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

GO

USE TestDB

GO

DELETE SampleDB.dbo.TableName1
INSERT SampleDB.dbo.TableName1
SELECT * FROM TableName1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rengsonsAuthor Commented:
hi Bodestone  thank u for ur repleys..
but my question is : i hav one button when i click this button dialog box will be opened and select .bak file and restore TESTDB only.
 (Inside button click i write followin code
USE MASTER  
GO  
RESTORE DATABASE [TestDB] FROM  DISK = 'TestDB_backup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10  
 )
its succesfully restore TESTDB no probs.
But i need  SAMPLEDB also changed..
0
BodestoneCommented:
That's what the second bit does.
It deletes the rows in SampleDB.dbo.TableName1and re-populates them from the newly restored database.
0
rengsonsAuthor Commented:
Last one month im tried to work this
But now only i complete with "Bodestone"..
Great Bodestone..
Thank u ... Thank u very much..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.