Need some help with triggers

• NewBornDBA2017

  SSCarpal Tunnel

  Points: 4832

  More actions

  April 18, 2018 at 2:58 pm

  #328467

  I have created a small table and inserted some records into it. Now I am just trying to test my trigger which I created but it is not working as expected. So what I did was I updated Fname where contactID = 6, but when I select * from contact, I see my name in updated column for all records and not just for contactID 6. How do I tackle this issue? I just want to keep track of who is changing what records.
  create table Contact
(
     ContactID int primary key
    ,FName varchar (50)
    ,LName varchar (50)
    ,CreatedBy VARCHAR (30) CONSTRAINT C1_DimEducation DEFAULT(SUSER_NAME())
    ,CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
    ,UpdatedBy varchar (30) CONSTRAINT C2_DimEducation DEFAULT(SUSER_NAME())
    ,UpdatedDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO

  Create trigger trg_Contact
on Contact
after update
as
set nocount on
update Contact
set UpdatedBy = SUSER_SNAME(),
UpdatedDate = GetDate()

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  April 18, 2018 at 3:11 pm

  #1987430

  NewBornDBA2017 - Wednesday, April 18, 2018 2:58 PM

  I have created a small table and inserted some records into it. Now I am just trying to test my trigger which I created but it is not working as expected. So what I did was I updated Fname where contactID = 6, but when I select * from contact, I see my name in updated column for all records and not just for contactID 6. How do I tackle this issue? I just want to keep track of who is changing what records.
  create table Contact
  (
       ContactID int primary key
      ,FName varchar (50)
      ,LName varchar (50)
      ,CreatedBy VARCHAR (30) CONSTRAINT C1_DimEducation DEFAULT(SUSER_NAME())
      ,CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
      ,UpdatedBy varchar (30) CONSTRAINT C2_DimEducation DEFAULT(SUSER_NAME())
      ,UpdatedDate DATETIME NOT NULL DEFAULT GETDATE()
  )
  GO

  Create trigger trg_Contact
on Contact
after update
as
set nocount on
update Contact
set UpdatedBy = SUSER_SNAME(),
UpdatedDate = GetDate()

  Well you have no where clause in your update so yes it's updating everything in the table.

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  April 19, 2018 at 12:02 am

  #1987451

  NewBornDBA2017 - Wednesday, April 18, 2018 2:58 PM

  I have created a small table and inserted some records into it. Now I am just trying to test my trigger which I created but it is not working as expected. So what I did was I updated Fname where contactID = 6, but when I select * from contact, I see my name in updated column for all records and not just for contactID 6. How do I tackle this issue? I just want to keep track of who is changing what records.
  create table Contact
  (
       ContactID int primary key
      ,FName varchar (50)
      ,LName varchar (50)
      ,CreatedBy VARCHAR (30) CONSTRAINT C1_DimEducation DEFAULT(SUSER_NAME())
      ,CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
      ,UpdatedBy varchar (30) CONSTRAINT C2_DimEducation DEFAULT(SUSER_NAME())
      ,UpdatedDate DATETIME NOT NULL DEFAULT GETDATE()
  )
  GO

  Create trigger trg_Contact
on Contact
after update
as
set nocount on
update Contact
set UpdatedBy = SUSER_SNAME(),
UpdatedDate = GetDate()

  Change your update statement as follows
  UPDATE c
SET UpdatedBy = SUSER_SNAME(),
  UpdatedDate = GetDate()
FROM INSERTED AS i
INNER JOIN Contact AS c
 ON i.ContactID = c.ContactID;

  ---

  How to post data/code on a forum to get the best help.