I need to populate a column with the date that another column in the same table was first populated and then have that date remain static even if the source column is updated. This date can be different for every row in the table.
asked
How do I use the population date of one row/column to populate the date in another row/column?
Use a trigger to to identify when the first column is populated, and then update the second column where it is not null.
Some example code:
use tempdb;
if object_id('YourTable') is not null drop table YourTable;
create table YourTable
(id int identity(1,1), col1 datetime, someothercol varchar(10), col2 datetime);
go
create trigger YourTableTrigger on dbo.YourTable
for insert, update
as
if update(col1)
begin
update YourTable
set col2=getdate()
where col1 is not null and col2 is null
and id in (select id from inserted);
end;
go
-- insert some data without populating the col1 column
insert into dbo.YourTable (col1, someothercol)
select top 10 null,'blah';
go 10
-- see that col2 remains unpopulated
select * from dbo.YourTable;
/*
id col1 someothercol col2
----------- ----------------------- ------------ -----------------------
1 NULL blah NULL
2 NULL blah NULL
3 NULL blah NULL
4 NULL blah NULL
5 NULL blah NULL
6 NULL blah NULL
7 NULL blah NULL
8 NULL blah NULL
9 NULL blah NULL
10 NULL blah NULL
(10 rows affected)
*/
-- update col1 in some rows
update dbo.YourTable
set col1='1 Jan 2018'
where id%2=1;
-- and see that col2 has been populated
select * from dbo.YourTable;
/*
id col1 someothercol col2
----------- ----------------------- ------------ -----------------------
1 2018-01-01 00:00:00.000 blah 2018-09-28 08:31:06.413
2 NULL blah NULL
3 2018-01-01 00:00:00.000 blah 2018-09-28 08:31:06.413
4 NULL blah NULL
5 2018-01-01 00:00:00.000 blah 2018-09-28 08:31:06.413
6 NULL blah NULL
7 2018-01-01 00:00:00.000 blah 2018-09-28 08:31:06.413
8 NULL blah NULL
9 2018-01-01 00:00:00.000 blah 2018-09-28 08:31:06.413
10 NULL blah NULL
(10 rows affected)
*/
-- update them again
update dbo.YourTable
set col1='2 Jan 2018'
where id%2=1;
-- but see that col2 has remained static
select * from dbo.YourTable;
/*
id col1 someothercol col2
----------- ----------------------- ------------ -----------------------
1 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
2 NULL blah NULL
3 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
4 NULL blah NULL
5 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
6 NULL blah NULL
7 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
8 NULL blah NULL
9 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
10 NULL blah NULL
(10 rows affected)
*/
-- now insert a single row with col1 poplated
insert into dbo.YourTable (col1, someothercol)
select '1 feb 2018','new blah';
-- and see that col2 gets populated too
select * from dbo.YourTable;
/*
id col1 someothercol col2
----------- ----------------------- ------------ -----------------------
1 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
2 NULL blah NULL
3 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
4 NULL blah NULL
5 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
6 NULL blah NULL
7 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
8 NULL blah NULL
9 2018-01-02 00:00:00.000 blah 2018-09-28 08:31:06.413
10 NULL blah NULL
11 2018-02-01 00:00:00.000 new blah 2018-09-28 08:31:06.417
(11 rows affected)
*/
Thanks! That's exactly what I was looking for. I was waaaay overthinking this.
18 People are following this question.
