trigger to change a field when the date changes

trigger to change a field when the date changes

Post by Jeff J » Fri, 21 Sep 2001 08:38:03



HI,

I'm trying to write a script to automatically change a field(avail)
from a 1 to a 0 once another field(date_to) in the table(tblmembers)
passes the date in the field.

I.E if the date_to has a value of 19/09/01, as soon as this day is
reached I want the avail field to change to 0.

I want to use T-Script but am new to the language.

Help to get started is much appreciated

Thanks

Jeff Joy

 
 
 

trigger to change a field when the date changes

Post by BP Margoli » Fri, 21 Sep 2001 09:10:20


Jeff,

You need to schedule a job that will run, presumably, daily that will check
the date and perform the UPDATE.

See the section "Scheduling Jobs" in the SQL Server Books Online for
additional information.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> HI,

> I'm trying to write a script to automatically change a field(avail)
> from a 1 to a 0 once another field(date_to) in the table(tblmembers)
> passes the date in the field.

> I.E if the date_to has a value of 19/09/01, as soon as this day is
> reached I want the avail field to change to 0.

> I want to use T-Script but am new to the language.

> Help to get started is much appreciated

> Thanks

> Jeff Joy


 
 
 

trigger to change a field when the date changes

Post by Wallace Cast » Wed, 26 Sep 2001 06:03:28


Does the following example produce the results you want? You can create a
job scheduled to run daily to run the UPDATE statement.

CREATE TABLE TBLMEMBERS (C1 INT, AVAIL BIT, DATE_TO DATETIME)
GO
INSERT TBLMEMBERS VALUES (1,1,'09/19/01')

UPDATE TBLMEMBERS SET AVAIL = 0
 WHERE CONVERT(VARCHAR(10),GETDATE(),101)
    >= CONVERT(VARCHAR(10),DATE_TO,101)

SELECT * FROM TBLMEMBERS

Here is the result set:

C1      AVAIL   DATE_TO
1       0       2001-09-19 00:00:00.000

Please do not send email directly to this alias.  This is our online
account name for newsgroup participation only. This posting is provided "AS
IS" with no warranties, and confers no rights. You assume all risk for your
use. ? 2001 Microsoft Corporation. All rights reserved.

 
 
 

trigger to change a field when the date changes

Post by David Russ, CN » Wed, 26 Sep 2001 13:27:21


Sounds like you need a job that kicks off at what ever cycle you need and updates both the date field and the other field.

David Russ, CNE, DBA
http://home.earthlink.net/~fdruss/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!