quote:

---

Originally posted by Michael Valentine Jones

Instead of the Insert trigger, create a default constraint on the date_added column with a value of getdate().

Or just take care of it in your insert stored procedure.

---

use pubs
set nocount on
go

create table junk (rowid int primary key, i int, dateAdded datetime null, dateUpdated datetime null)
go

create trigger junk_tr_insUpd on junk after insert, update
as
begin
	--update
	update j set
		j.dateUpdated = getdate()
	from	junk j
	join	deleted d
			on d.rowid = j.rowid
		
	--insert
	update j set
		j.dateAdded = getdate()
	from	junk j
	join	inserted i
			on i.rowid = j.rowid
	left join deleted d
			on d.rowid = i.rowid
	where	d.rowid is null
end
go

insert junk (rowid, i)
select 1, 10 union
select 2, 20 union
select 3, 30

select * from junk

update junk set
	i = 200
where	rowid = 2

select * from junk

go
drop table junk

quote:

---

Originally posted by TG

Ideally, posting to the table only happens via stored procedures and direct table access is not allowed to users. In that case you can handle both date columns with the SPs. However, if that is not possible then I would combine the logic in a single trigger. (as I mentioned above)

use pubs
set nocount on
go

create table junk (rowid int primary key, i int, dateAdded datetime null, dateUpdated datetime null)
go

create trigger junk_tr_insUpd on junk after insert, update
as
begin
	--update
	update j set
		j.dateUpdated = getdate()
	from	junk j
	join	deleted d
			on d.rowid = j.rowid
		
	--insert
	update j set
		j.dateAdded = getdate()
	from	junk j
	join	inserted i
			on i.rowid = j.rowid
	left join deleted d
			on d.rowid = i.rowid
	where	d.rowid is null
end
go

insert junk (rowid, i)
select 1, 10 union
select 2, 20 union
select 3, 30

select * from junk

update junk set
	i = 200
where	rowid = 2

select * from junk

go
drop table junk

Be One with the Optimizer
TG

---