October 26, 2015 at 12:07 pm
CREATE TABLE EDI_data_proc_log(
ID int IDENTITY(1,1),
comment VARCHAR(3000),
time_recorded DATETIME DEFAULT GETDATE(),
run_by varchar(100),
duration int );
When a record is inserted I like the duration column to be computed.
This should happen only after the first record to the table has gotten inserted.
You might say a trigger would be the best.. Ok then, show me the syntax.
Or I am thinking can we write a user defined function that will compute the value for the duration column.
--By default, I would like to update the duration column as follows:
--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )
October 26, 2015 at 1:36 pm
Would this produce the correct behavior?
CREATE TABLE EDI_data_proc_log(
ID int IDENTITY(1,1),
comment VARCHAR(3000),
time_recorded DATETIME DEFAULT GETDATE(),
run_by varchar(100),
duration int );
GO
CREATE TRIGGER TI_EDI_data_proc_log ON EDI_data_proc_log
INSTEAD OF INSERT
AS
INSERT INTO EDI_data_proc_log(
comment,
time_recorded,
run_by,
duration
)
SELECT comment,
getdate(),
run_by,
DATEDIFF( ss, (SELECT MAX( time_recorded) FROM EDI_data_proc_log), GETDATE())
FROM inserted;
GO
INSERT INTO EDI_data_proc_log(comment, run_by) VALUES ('Test1', 'User');
WAITFOR DELAY '00:00:01';
INSERT INTO EDI_data_proc_log(comment, run_by) VALUES ('Test2', 'User');
WAITFOR DELAY '00:00:02';
INSERT INTO EDI_data_proc_log(comment, run_by) VALUES ('Test3', 'User'),('Test4', 'User');
GO
SELECT * FROM EDI_data_proc_log;
GO
DROP TABLE EDI_data_proc_log;
October 27, 2015 at 12:06 am
Use getutcdate() not getdate() or you'll run into problems due to daylight savings time. Like last weekend, when the clock was reset back from 03:00 to 02:00.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply