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?
 
BodestoneConnect With a Mentor Commented:
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
 
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
 
BodestoneConnect With a Mentor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.