Sql CretedDate Trigger

Question

This is the query I used for creating a trigger to update the CreatedDate column of my table "websites"

create trigger [dbo].[trgrforcreateddate] on [dbo].[Websites] 
after insert
as
update dbo.websites 
set CreatedDate=getdate() from websites w inner join inserted i on w.website=i.website where w.website=i.website

It worked, only one should get updated with Created date (actually, the expected row is updated). But as a result I see " rows updated"

Why?

Answer 1

For this you should be using a default constraint on CreatedDate instead of a trigger.

alter table dbo.websites add constraint df_websites_CreatedDate default getdate() for CreatedDate;

---

The trigger is not joining on a unique id, if it was you would see only 1 row affected for each insert. You should also use set nocount on; to prevent extra row result messages from being returned, but in this case it was good that it was not set so that you noticed the error.

alter trigger [dbo].[trgrforcreateddate] on [dbo].[Websites] 
after insert
as
begin;
  set nocount on;
  update w
  set w.CreatedDate=getdate() 
  from dbo.websites w
    inner join inserted i
      on w.id = i.id;
end;