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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-12 : 08:36:21
|
| shailesh writes "I am using SQL Server 7.0I am having one table temp1 in the pubs database and another table temp2 in the tempdb database. Both the tables have same data structure.( i.e. id int(4), name varchar(20) ) I want to update the table temp2 inthe tempdb database as soon as some changes occured in the table temp1 in the pubs database. How do I can achieve these ?Plz, is it possible to do these?and How ?" |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-12 : 09:27:57
|
| Well my first thought was you could use a trigger, but according to BOL, you can't use a trigger on a temporary table. So, my only conclusion here is that the update statment for temp1 should then fire off an update statment in the same stored procedure to pass those changes to temp2. I'd also question, what you're doing that makes this level of complication necessary in the first place!Mike "A program is a device used to convert data into error messages." |
 |
|
|
shaileshu
Starting Member
2 Posts |
Posted - 2001-12-14 : 08:41:13
|
| I am having one table temp1 in the pubs database and another table temp2 in the tempdb database. Both the tables have same data structure.( i.e. id int(4), name varchar(20) ) I want to update the table temp2 inthe tempdb database as soon as some changes occured in the table temp1 in the pubs database. How do I can achieve these ?Plz, is it possible to do these?and How ?"Plz,Is it possble for to provide the script required for writing such a trigger and procedure for updating the tables in two different databases.then plz forward it to me.shaileshu |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-14 : 11:31:17
|
| You can get the proper syntax for creating a trigger by looking up CREATE TRIGGER in BOL. Inside the trigger, you'll need to use 3-part naming for your tables (i.e. databasename.ownername.tablename). You'll end up with something that looks likeUPDATE tempdb.dbo.temp2 SET field1 = temp1.field1BUT, if either of these really are temporary tables, prefixed with #, then I don't think any of this will work.And as Mike says, what are you really trying to accomplish? Why are you doing anything directly in the tempdb database? Sounds awfully dangerous or convoluted to me.-------------------It's a SQL thing...Edited by - AjarnMark on 12/14/2001 11:32:58 |
|
|
|
shaileshu
Starting Member
2 Posts |
Posted - 2001-12-15 : 03:39:26
|
| Right now, I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.shaileshu |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-15 : 05:04:50
|
| write triggers on update,insert and delete event on your A database table's .something like this should help.CREATE TRIGGER table_upd ON dbo.table1FOR UPDATE ASupdate database.owner.table set field1=n.field1,field2=n.field2 from inserted n something like this should help u out-------------------------"Success is when Preparedness meets Opportunity" |
|
|
|
|
|
|
|
|
Updating of the tables in two different databases.