The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
Trigger on a timer
Discuss Trigger on a timer in the MS SQL Development forum on Dev Shed. Trigger on a timer MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

September 27th, 2003, 09:53 AM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 5
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Trigger on a timer
Can anyone explain how to fire a trigger based on a timer (say...every 30 seconds)? Can this be done completely within SQL code?
TIA
|

September 27th, 2003, 11:16 AM
|
 |
Banned ;)
|
|
Join Date: Nov 2001
Location: Woodland Hills, Los Angeles County, California, USA
|
|
|
If it is ok to execute every minute, then look into creating a job (minimum interval is 1 minute for jobs). Search your Books Online for "jobs, creating", "jobs, defining" and "jobs, scheduling". Hope this helps
__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
|

September 27th, 2003, 02:39 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 5
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
ahhh-hah!
Great point, man. I'm not sure a job will handle what I need, tho. I need to copy data from two tables in one database to a table in another database. Can jobs operate across databases (both are MS SQL)?
BTW, thanks for the help!
|

September 27th, 2003, 02:45 PM
|
 |
Banned ;)
|
|
Join Date: Nov 2001
Location: Woodland Hills, Los Angeles County, California, USA
|
|
Heck yeah, you can access data from databases on two different servers if you had to. From database1, you can do
SELECT * FROM database2.dbo.table2
or
SELECT * FROM database2..table2
If database2 is on a different server, you can do something like
SELECT * FROM server2.database2.dbo.table2
Only thing is you have to tell server1 about server2 first with sp_addlinkedserver.
However, if you just want to copy data from one server to another, consult your Books Online for "replication". HTH 
|

September 27th, 2003, 03:11 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 5
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Wonderful!
Thanks for the help, man!
I think I'll create a job instead of replication. Looking at the books online, it looks like a few additional management things need to happen to setup replication properly. It seems a job would be less invasive. Also, I can set the job up to notify the operator if the job fails, right?
I don't mean to take advantage of your willingness to help but what would the syntax for such a query look like? Can you perform a SELECT on database1.dbo.table1 and then, in the same query do an UPDATE on database2.dbo.table2? Thus, moving the data from one database to another.
<Not to get too greedy but> what if I only wanted to update the data that had populated the table since my last job ran. This way, I'm not selecting every row in the table and moving it each time.
Once again, thanks!
|

September 27th, 2003, 03:28 PM
|
 |
Banned ;)
|
|
Join Date: Nov 2001
Location: Woodland Hills, Los Angeles County, California, USA
|
|
>> Can you perform a SELECT on database1.dbo.table1 and then, in the same query do an UPDATE on database2.dbo.table2?
Code:
UPDATE t1
SET t1.field1 = t2.field1,
t1.field2 = t2.field2
FROM database1.dbo.table1 AS t1
INNER JOIN database2.dbo.table2 AS t2
ON t1.key_field = t2.key_field
You can also write a stored proc to do the transformation or whatever and then hook the stored proc to the job.
|

September 27th, 2003, 03:47 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 5
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Okay...okay....
I gotcha! I've gotta look-up more info on "INNER JOIN". That's great stuff, man.
Correct me if I'm wrong tho - your code would return all data in the respective columns regardless of age, right. For example, if in your code, "field1" were usernames and "field2" were passwords: your example would update database2 with EVERY username and password from database1 each time that the job ran, right? If the databases are large and this job runs every 5 minutes, would that create some latency? If so, is it much more complicated to only update the usernames and passwords that were added to database1 since the last job ran?
In any case, I REALLY appreciate the help.
|

September 27th, 2003, 03:53 PM
|
 |
Banned ;)
|
|
Join Date: Nov 2001
Location: Woodland Hills, Los Angeles County, California, USA
|
|
|
There are quite a few ways to do this:
1. Modify the join clause to only select from table1 where fields are not in table2
2. Create a stored proc to select stuff from table1 into a temp table. Then delete all the rows from the temp table that exist in table 2. Then insert the remaining rows from temp table into table 2
Seriously though, I think you should really look into replication, because it is designed to do precisely what you need (i.e.) insert rows that don't exist in one database from another database.
|

September 27th, 2003, 04:10 PM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 5
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
U R Da MAN!
MAN! The help is MUCH appreciated! I think I have enough info to tackle this beast.
I will do more research on replication but I have always thought of it as something that is "bulky" and cumbersome - not something you have run every 5 minutes. I know that replication has come a long way over the last few SQL versions but I still view it as a way to backup data or update a remote database once every couple hours.
Sincerely man, thanks for the help!
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|