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 |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2002-11-01 : 17:39:24
|
| I want to create a trigger in a table within Northwind that fires when a certain field in a different table from Pubs is updated. How do you reference a different column from a different database within the trigger create statement? MBeal |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-01 : 17:44:16
|
| Hmm, I'm not quite sure if you've got the trigger concept right here. Your update trigger needs to be on the table thats being updated. If the table in your pubs database is being updated... the trigger will need to be on that table.-----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-11-01 : 17:48:50
|
| The problem is that (in this example) the Pubs table may be completely updated and changed by the software developer -- meaning my trigger will be lost the next time we get an update. So my hope was that I could create a trigger in a different database and update that same database when a field from a different database was changed. Clear as mud -- right?MBeal |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-01 : 17:56:33
|
| Well, I don't know of any way to do that except to create a scheduled job that runs periodically to check the status of the pubs table. Even then you'd have a helluva time figuring out if and when and what was updated.Just script out the trigger code and keep it in a safe place. You can always run it periodically to create the trigger. Heck, you could put the CREATE TRIGGER code in a scheduled job and let it run once a week or something like that. |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-11-01 : 18:00:08
|
| Thanks -- MBeal |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-01 : 18:01:17
|
 Heh, very clear. Unfortunately no... Triggers exist and fire on one table only. So your saying the software developer is dropping and recreating the table without your trigger on it? Hmm.. smack around the developers asking why they are dropping objects rather then altering them. That drop will also kill foriegn keys, constraints, indexs and anything else you've made on the table unless they are specifically recreating them every time.I guess you could moniter the sysobjects table for the create date of the table and go off that. Unfortunately triggers will not be your answer here-----------------------SQL isn't just a hobby, It's an addictionEdited by - m.e. on 11/01/2002 18:03:19 |
|
|
|
|
|
|
Triggers outside of current database