Post reply

Creating On Date change trigger???

  • December 22, 2006 at 4:06 am

    #176070

    I want to update my table values depending upon the system date. How can i create a database trigger to change the values in the table when the system date changs??? Please help as soon as possible.

  • December 22, 2006 at 4:22 am

    #679678

    Can you explain what you mean by system date change. You know every second the date is change and what you want to achieve by this. Detail description of problem will find out the best solution for u

    cheers

  • December 22, 2006 at 8:00 am

    #679728

    Just a hunch :  maybe you could set up a job that runs each day at midnight and do the updates you need??

  • December 27, 2006 at 12:30 am

    #680043

    actually i want to update the column in a table when the a specific date arrives. You can say that i want to expire a person's login when a specific date arrives.

  • December 27, 2006 at 9:34 am

    #680116

    As I said, run a job a midnight every night.

    Then in the job run a statement like this one :

    UPDATE dbo.Logins SET IsInactive = 1 WHERE "Whatever you need to satisfy here"

  • December 27, 2006 at 10:00 pm

    #680207

    As i am new to T-SQL and i don't know how to create a job. Can you please explain me or send me an example of it.

  • December 27, 2006 at 11:24 pm

    #680213

    I don't have SQL Server 2005 but this is how you do it in SQL Server 2000:

    (If you really want to set up a job for this, that is.

    You can probably just work off an expiration date field to find the active "logins" for your queries.

    Oh well... It's good practice, anyway)

    In Enterprise Manager, find your server (if it's not in your group already, register it).

    Underneath the server, go to Managemant>SQL Server Agent>Jobs.

    Right-click on it and select New Jobs... from the context menu.

    Give it a name (Nightly whatever), go to the Steps tab and click New....

    This is where you put in your script (make sure it's right).

    Give the step a name (and go check out the Advanced tab, if you want).

    Hit OK. (For more complex stuff you would add more steps, have step1 kick off step2 etc..., or use more complex stored procedures or scripts.)

    Go to the Schedules tab.

    Click on New Schedules...

    Give it a name (Nightly whatever), set it for Recurring> and hit "Change..."

    select Daily and midnight (you can pick a closer time for a test and then change it back to midnight later)

    Hit OK.

    Go back to the General tab check the Enabled checkbox and hit OK to finalize your job.

    Your newly created Job should appear under the list of jobs back at Managemant>SQL Server Agent>Jobs.

    Make sure that there are no other old jobs in the list of jobs that are enabled that you wouldn't want running;

    If there are, and you are sure that it's safe, disable them.

    Go to Management>SQL Server Agent, check if the service is started (green Play button icon).

    Right click on it (another way to know if it's started is that Stop shouldn't be grayed out) and select Properties from the context menu.

    Make sure that the SQL Server Agent service has the same startup account/password as the one SQL Server itself is running under.

    Hit OK.

    If the service was stopped, right click on it and start it up.

    I'm sure there are a lot of articles and forum posts you can find on this topic if you do a quick search.

  • January 11, 2007 at 9:55 pm

    #682808

    Thanks....It worked for me. Thanks again.

  • January 12, 2007 at 8:04 am

    #682912

    a calulated column would be better.

    alter tabe table

    add isactve case when dateexpires >= getdate then 0 else 1 end

     

    something like that

    This is more robust than the nightly sql agent job but relys of course that the column(s) you need to calc inactive is in the same table

    www.sql-library.com[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply