#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep 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
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    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

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep 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!
  6. #4
  7. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    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
    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

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep 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!
  10. #6
  11. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    >> 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.
    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

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep 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.
  14. #8
  15. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    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.
    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

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep 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!

IMN logo majestic logo threadwatch logo seochat tools logo