Trigger fired by a calculated field??
-
I have a table of people, including birthdays, and I'd like to insert a record into a separate table when the person turns 18. In concept, what I'd like to do is have a calculated age field that fires a trigger when it changes, and the trigger would check to see if the person turned 18. If so, it inserts the record.
However, this doesn't work because calculated fields don't get updated, they are simply calculated. Is there any workaround? What is the accepted solution for a situation like this where something needs to happen a specified amount of time after a datetime?
Thanks!!
-
1. Are you wanting this to occur after an UPDATE occurs on the row?
OR
2. Are you wanting this to occur when the current time makes the calculated age field tick over to 18 ?
For 1. you just need to add some more code to your updating sprocs, or a trigger on the table.
For 2. you need to create some code that is executed regularly (perhaps daily) to detect what people have recently become 18. Use a SQL Agent Job.
Basically Time creates no events in the database. The Calculated column isn't stored, and doesnt' change. It's calculated at the moment you SELECT information from the table. Becuase SQL Server doesn't UPDATE that column, there is no event to detect when it changes.
Julian Kuiters
juliankuiters.id.au -
Just out of interest, how would you use a SQL AGENT job?
-
You can create a Sql Agent job that was scheduled to execure everyday,, with a step to executed a T-SQL command.
The SQL Command could be set to insert a record in the table for all people who's 18th birthday was today.
So long as the T-SQL command is run every day, the event would be actioned every day.
You can create a SQL Agent Job in Enterprise Manager (under Management, SQL Server Agent, Jobs... just right click and create a new job). Read up about creating jobs in SQL Books Online / Help.
Julian Kuiters
juliankuiters.id.au -
I have a table of people, including birthdays, and I'd like to insert a record into a separate table when the person turns 18. In concept, what I'd like to do is have a calculated age field that fires a trigger when it changes, and the trigger would check to see if the person turned 18. If so, it inserts the record.
I might be missing a piece in the puzzle here, but such a requirement sounds a bit strange to me. May I ask what you want to do exactly?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hey guys - thanks for the responses!
What I'm trying to do is keep a table with human-readable history for each person in my people table. Whenever significant events happen on one of several tables, I insert a record into this human-readable history table.
One such record I'd like to be triggered is when a person turns 18. That is why the record needs to be inserted as soon as they turn 18, rather than the next time that record is updated.
I may not always use the correct terminology (record vs. row, field vs. column, etc.) since I have had no formal training in SQL, but I consider myself an intermediate SQL user nonetheless.
My original post was to explain that I know why you can't have a trigger fire as soon as someone turns 18 - because the calculated age field is never UPDATEd - but that since I still need it to happen, what are some alternative solutions. SQL Agent sounds like it might be good. I'll investigate that.
Thanks again!
-
The 18 birthday might be not the best example. Marriage, Birth of children, Divorce (nono... no particular order here
) are maybe better examples. Calculating the age can be pretty easily done via a view. There should be no need to store this. Anyway, if you insist on doing this. What about a job that once a day scans the table and insert matching "aged" people data into your other table. Here are some sample to calculate the age:
DECLARE @geburtstag DATETIME
SET @geburtstag = '23.07.1968'
SELECT
DATEDIFF(yyyy,CAST(@geburtstag AS DATETIME),GETDATE())
-
(CASE WHEN DATEADD(yyyy,DATEDIFF(yyyy,CAST(@geburtstag AS DATETIME),GETDATE()),CAST(@geburtstag AS DATETIME)) >
GETDATE() THEN 1 ELSE 0 END)
SELECT
(0+CONVERT(CHAR(8),CURRENT_TIMESTAMP, 112)
-
CONVERT(CHAR(8), @geburtstag, 112))/10000
SELECT
YEAR(GETDATE()) - YEAR(@geburtstag) -
(CASE
WHEN (MONTH(@geburtstag) > MONTH(GETDATE()))
OR (MONTH(@geburtstag) = MONTH(GETDATE())
AND DAY(@geburtstag) > DAY(GETDATE()))
THEN 1 ELSE 0 END)
SELECT
(CAST(CONVERT(CHAR(8),GETDATE(), 112) AS INT)
-
CAST(CONVERT(CHAR(8), @geburtstag, 112) AS INT))/10000
SELECT
DATEDIFF(yy, @geburtstag, GETDATE()) -
CASE
WHEN (MONTH(GETDATE()) * 100 +
DAY(GETDATE())) <
(MONTH(@geburtstag)* 100 + DAY(@geburtstag))
THEN 1 ELSE 0
END
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Okay, I have a trigger on my people table that looks like this:
CREATE TRIGGER insertTransactionTurned18
ON dbo.people
FOR UPDATE
AS
DECLARE @personid int,@oldage int,@newage int
SELECT @oldage = age
FROM deleted WHERE active=1
SELECT @newage = age,@personid = id
FROM inserted WHERE active=1
IF (@oldage = 17) AND (@newage = 18)
BEGIN
INSERT INTO transactions (person,type,details,addedon)
VALUES (@personid,3,'Associate turned 18',getdate())
END
I also have a job with one step that runs at midnight that looks like this:
UPDATE people SET age=CASE
WHEN dateadd(year,datediff(year,birthdate,getdate()),birthdate) > getdate()
THEN datediff(year,birthdate,getdate()) - 1
ELSE datediff(year,birthdate,getdate())
END
For some reason, the trigger works perfectly only when I set the age explicitly (i.e. UPDATE people SET age=18 WHERE age=17). It *doesn't* fire, however, when my real update statement runs (UPDATE people SET age=CASE WHEN...), either manually or as part of a job, even though many of the people's ages turn from 17 to 18.
What am I doing wrong?
Thanks!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply