SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Should I use a Trigger or Stored Proc for this? If so How? Thanks

    I have a table that maintains the warranty status of products. The default state for 'statusA' for each product warranty is 'active' and 'statusB' is 'expired'
    The life span is calculated by using 'dateAdd' function to add the warranty length.
    ALL THIS WORKS FINE.
    What I want is for the "wStatus" field to change to "expired" whenever the "expired_date" field or status occurs.
    That is, I want to automate the 'wstatus' field state based on the weather the warranty is expired or active.
    I believe this is a job for a trigger, but I don't know how to do it.

    Here is my table

    CREATE TABLE [dbo].[warranty] (
    [warrantyId] [int] IDENTITY (1, 1) NOT NULL ,
    [statusA] [varchar] (15) NOT NULL ,
    [statusB] [varchar] (15) NOT NULL ,
    [effective_date] [datetime] NOT NULL ,
    [expire_date] [datetime] NOT NULL ,
    [wStatus] [varchar] (15) NOT NULL, DEFAULT 'Active'
    ) ON [PRIMARY]
    GO

    Thanks,
    assigned

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why?

    let's say that tomorrow, seventeen products are about to expire

    if you wanted to flag all of them as expired, you'd have to run an UPDATE with a WHERE condition to find them, and it would be based on comparing getdate() to expire_date

    why does the status have to be stored? why not calculate it whenever you do a query?

    for example, if you wanted to find out which products expire next month, that's a SELECT with a similar WHERE clause, using getdate() and dateadd()...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's a great Idea rudy, but the problem is that users are constantly viewing the status on a daily basis. Wouldn't such a query place a heavy load on the system?
    I am storing the status simply for that. So if a user views the page today and the product is still under warranty, it displays "active". If by tomorrow, the expiration date comes due as calculted, then I think a trigger or SP, should simply do the update.
    The truth is that I don't know how to do it either the way you are suggesting or the method I am visualizing. I will appreciate any code samples.
    Thanks
    assigned

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here's some code -- specifically, a VIEW definition that mimics that extra column
    Code:
    create view warranty_v
    as
    select warrantyId
         , statusA
         , statusB
         , effective_date
         , expire_date
         , case when expire_date < getdate()
                then 'expired' else 'active'
                end
            as wStatus
      from warranty
    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •