February 6, 2009 at 8:30 pm
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
February 6, 2009 at 9:11 pm
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
February 6, 2009 at 9:15 pm
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
February 6, 2009 at 9:19 pm
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
February 9, 2009 at 6:32 am
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?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2009 at 7:02 am
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
February 9, 2009 at 7:07 am
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
February 9, 2009 at 8:25 am
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.
February 9, 2009 at 8:50 am
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
February 9, 2009 at 11:27 am
Vijaya Kadiyala (2/9/2009)
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
February 9, 2009 at 1:16 pm
Vijaya Kadiyala (2/9/2009)
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
February 9, 2009 at 1:21 pm
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
February 9, 2009 at 1:31 pm
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/
February 9, 2009 at 2:15 pm
[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]
February 9, 2009 at 2:52 pm
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