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

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

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!

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

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!

>> Can you perform a SELECT on database1.dbo.table1 and then, in the same query do an UPDATE on database2.dbo.table2?


You can also write a stored proc to do the transformation or whatever and then hook the stored proc to the job.

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.

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.

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!