update field trigger on date change

[jermine]

i need to create a trigger that will update the status field in my table to 'A' (active) when its setToActive date is equal to the current date. The default value of the status field is 'I' (inactive).

i have attached my code below but please see if this would work and do the trick.

CREATE TRIGGER dbo.TRG_MakeActive ON [dbo].[tbQuestion]
FOR UPDATE

AS
BEGIN
DECLARE @idQuestion int

SELECT @idQuestion = IdQuestion
FROM tbQuestion
WHERE convert(char(10),startDate, 101) = convert(char(10),getdate(), 101))

if (@idQuestion != null)
(
UPDATE tbQuestion
SET Active = 1
WHERE idQuestion= @idQuestion
)


END

Thanks in advance guys!

 

[donutman]

From how you describe your requirements, to me it sounds like you want a scheduled job not a trigger. Triggers are run when a row is Updated, Inserted or Deleted. Is is correct to say that your table has rows in it that should just go "active" one second after midnight on a particular day?
If so write a stored procedure that scans the entire table and updates the column "Active" to 'A' when appropriate.

UPDATE tbQuestion
SET Active='A'
WHERE StartDate<GetDate()

and run that SP just after midnight as a scheduled job. You may want to tweak the where clause if that's not exactly what you want. It's also possible that you may want to also create an Insert and/or Update trigger in case a record gets added or updated with Active='I' when it should have been 'A'. But if that's true then why doesn't the application just "fix" the Active column?
-Karl

 

[jermine]

thanks donutman for that quick fix!