I have a trigger that supposed to update log out time[generate random log out time within an hour of log in time, seconds have to differ too], how do I achieve that functionality, please help here is my trigger so far:
USE TestDB
GO
CREATE TRIGGER AfterInsertTG
ON dbo.usage_reports_Dummy2
AFTER INSERT AS
DECLARE @pk_id as int, @member_id as int,@login_time AS DATETIME,@logout_time AS DATETIME
,@ip AS VARCHAR(255),@session_id AS VARCHAR(255);
SELECT
@pk_id = pk_id ,
@member_id = member_id,
@login_time =login_time,
@logout_time = logout_time,
@ip = ip,
@session_id = session_id
FROM
usage_reports_Dummy2
IF(@logout_time IS NULL)
BEGIN
???????
END
GO
Thank you all for helping me out specially Eric for taking some time and think about the formula, I chose Marc's answer becuase it suits my conditions
here is the final code:
CREATE TRIGGER trgInsert
ON dbo.usage_reports_Dummy2
INSTEAD OF INSERT
AS BEGIN
INSERT INTO
dbo.usage_reports_Dummy2(member_id, login_time, logout_time, ip, session_id)
SELECT
member_id, login_time, logout_time, ip, session_id
FROM inserted
WHERE logout_time IS NOT NULL
INSERT INTO
dbo.usage_reports_Dummy2(member_id, login_time, logout_time, ip, session_id)
SELECT
member_id, login_time, DATEADD(ss, RAND() * 3600, login_time),
ip, session_id
FROM inserted
WHERE logout_time IS NULL
END