Reputation: 1841
I would like to know how to check whether a timestamp in a (StartTime Column) is greater than the system time. If greater, it should reset the time stamp into NULL
Is there a way to do that?
Regards.
Upvotes: 1
Views: 966
Reputation: 445
Try...
CREATE TRIGGER tr_Product_InsteadOfTrigger
ON Product
INSTEAD OF INSERT
AS
IF @@ROWCOUNT > 0 -- limit nested recursion if no work to do
BEGIN
SET NOCOUNT ON;
INSERT INTO Product
(
ProductId,
PTime
)
SELECT
i.ProductId,
CASE
WHEN i.PTime > GETUTCDATE()
THEN NULL
ELSE i.PTime
END
FROM
inserted i
END -- if work to do
GO
Upvotes: 0
Reputation: 10763
create trigger tbl_reset_date
on tbl
after insert
as
update t
set time = null
from tbl t
join inserted i
on i.id = t.id
where i.time > getdate();
Upvotes: 1
Reputation: 57603
In MySQL you could try:
UPDATE table SET time = NULL
WHERE time>NOW()
If you want it to be auto, try this:
CREATE TRIGGER upd BEFORE INSERT ON `table`
FOR EACH ROW BEGIN
IF NEW.time>NOW() THEN SET NEW.time=NULL; END IF;
END;
Upvotes: 1
Reputation: 4193
Declare @Now datetime = GETDATE()
Update tbl Set
StartTime=NULL
Where StartTime > @Now
Upvotes: 1
Reputation: 56407
update table set ts_field = null where ts_field > now()
this is for mysql
Upvotes: 1