Trigger Problem-Urgent

  • When Expiry Date reach current system date, the record is expierd, At that time automatically status is change False value.

    It will invoke automatically when system date changes

    How to write trigger for it

    example TABLE: TEST

    SID int

    SNAME varchar

    Expirydate datetime

    Status bit

  • Triggers work off of DML actions being performed against the table, i.e. insert, update, delete. They don't fire off randomly or on a schedule.

    It sounds like you need to write an update statement that is going to find all the records that expired and then put that in a job to run periodically.

    If I am misunderstanding your request please rephrase the question and hopefully there will be help available.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • you don't need a trigger for this...you could handle it automatically by making status a calculated field instead of a bit you have to update:

    [font="Courier New"]CREATE TABLE TEST(

        SID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,

        SNAME VARCHAR(30),

        Expirydate DATETIME,

        Status AS CASE WHEN  Expirydate < GETDATE() THEN 1 ELSE 0 END )

    INSERT INTO test (sname,Expirydate)

    SELECT 'early','2007-12-25' UNION ALL

    SELECT 'middle','2009-02-5' UNION ALL

    SELECT 'late','2009-02-10'

    SELECT * FROM test

    /*

    results:

    1  early   2007-12-25 00:00:00.000 1

    2  middle  2009-02-05 00:00:00.000 1

    3  late    2009-02-10 00:00:00.000 0

    */

    [/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Nice, clearly I wasn't thinking!!! Thanks!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Lowell (2/6/2009)


    you don't need a trigger for this...you could handle it automatically by making status a calculated field instead of a bit you have to update:

    Hey, that's a nice tip. I'm wondering how does it scale?

  • Simple calculated fields perform pretty well and scale just as well as querying the column. And they're often more reliable than update jobs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm going to try to play with this later today to see what shows up in profiler and I will post back the results (unless one of you fine gentlemen decide to do it before me which would not sadden me at all :)) as I am most curious.

    Thanks!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Lowell, great tip on the calculated columns and I would expect these to perform much better than triggers.

    David, let us know if you find something. Or write an article :), heck write one either way if you're testing.

  • Thats really a good tip. This will work if you are creating a new table.

    What if the table already exists, so in this case we have to use Update Statement right!!

    Thanks -- Vijaya Kadiyala

    http://dotnetvj.blogspot.com

  • Vijaya Kadiyala (2/9/2009)


    Thats really a good tip. This will work if you are creating a new table.

    What if the table already exists, so in this case we have to use Update Statement right!!

    Thanks -- Vijaya Kadiyala

    http://dotnetvj.blogspot.com[/quote%5D

    Yep.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Vijaya Kadiyala (2/9/2009)


    Thats really a good tip. This will work if you are creating a new table.

    What if the table already exists, so in this case we have to use Update Statement right!!

    Thanks -- Vijaya Kadiyala

    http://dotnetvj.blogspot.com[/quote%5D

    OR replace the actual column for a calculated one 😀


    * Noel

  • Vijaya Kadiyala (2/9/2009)


    What if the table already exists, so in this case we have to use Update Statement right!!

    Why?

    Alter Table Add .... and add the computed column to the existing table. Since it's computed, the column doesn't actually store data and can't be updated. It's only calculated when the data is retrieved

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GSquared (2/9/2009)


    Vijaya Kadiyala (2/9/2009)


    Thats really a good tip. This will work if you are creating a new table.

    What if the table already exists, so in this case we have to use Update Statement right!!

    Thanks -- Vijaya Kadiyala

    http://dotnetvj.blogspot.com[/quote%5D

    Yep.

    Actually there is no need to run an update statement on a computed column. You can run the script bellow instead of taking my word for it. By the way not only that you don't need to run the update statement, you can't run it at all on computed columns.

    create table MyTable (i int)

    go

    insert into MyTable (i)

    select 10 union select 100

    go

    alter table MyTable add ComputedCol as i/2

    go

    select * from MyTable

    go

    drop table MyTable

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • [font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀

    And of course you can index calculated columns. So all in all, the calculated column is a far better solution.

    [/font]

  • GilaMonster (2/9/2009)


    Vijaya Kadiyala (2/9/2009)


    What if the table already exists, so in this case we have to use Update Statement right!!

    Why?

    Alter Table Add .... and add the computed column to the existing table. Since it's computed, the column doesn't actually store data and can't be updated. It's only calculated when the data is retrieved

    I took his question as meaning the table already exists and there is already a column in it for that data.

    Yes, the column can be dropped and rebuilt, but you have to make darn sure that there is no code trying to update that column if you're changing from data storage to calculated. That can be complex in some environments, where not all the database code is in procs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 22 total)

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