I have a table. In this table there are three columns I am interested in creating a trigger on.
My table - tblABC
column name data type
Id int (primary key)
Live bit
CompletionDate date
CancelDate date
So I would like the trigger to execute whenever the CompletionDate or CancelDate is updated.
I want the trigger to do one of two things
1) if either the CompletionDate or CancelDate columns date is changed to a date that is equal to today or less then I want the Live column to equal 0.
2) if either the CompletionDate or CancelDate columns date is changed to a date that is greater than today or NULL then I want the Live column to equal 1.
Below is my code. I am not sure if its correct also I'm not sure how this will deal with NULLS?
CREATE TRIGGER TR_MYTRIGGER
ON tblABC
AFTER UPDATE
update d
set d.Live = 0
from tblABC e d
inner join inserted i on d.Id = i.Id
where i.CompletionDate <= GETDATE() or i.CancelDate <= GETDATE()
update d
set d.Live = 1
from tblABC d
inner join inserted i on d.Id = i.Id
where i.CompletionDate > GETDATE() and i.CancelDate > GETDATE()
As you said in your second point, if either of the two dates is set to greater than today or set to null then you need to set your live column to 1. Change your second update query to this.
update d
set d.Live = 1
from tblABC d
inner join inserted i on d.Id = i.Id
where (i.CompletionDate > GETDATE() or i.CancelDate > GETDATE()
or i.CompletionDate is null or i.CancelDate is null) and d.Live <> 1
But as suggested in the comments, a computed column seems like a better option.
Edit For performance optimization, you shouldn't update the rows that are already 1. See updated query. Do the same for your first update query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments