• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Update Date get updated in all rows

hi,

This is the store proc am using to update and insert my table (credit goes to AngelIII),i have column in my table called updatedDate,when ever the record is get updated it will leave the time stamp there,but whenever the store proc runs it will update all the UpdatedDate column.

I am trying to update the UpdatedDate column only the record gets updated,some where am missing some thing,please correct me.

Thanks in Advance

ALTER PROC [dbo].[InsertPhone]
( @EmployeeNo int
, @PhoneNumber char(24)
)
as
begin
declare @CONTEXT_INFO varchar(100)
select @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

UPDATE o 
  SET o.employeeno = c.employeeno 
    , o.PhoneNumber =c.PhoneNumber
,o.DateUpdated=getdate()
    , o.CreatedBy=@CONTEXT_INFO
    , O.UpdatedBy=@CONTEXT_INFOT 
 FROM Phone o 
 JOIN ADUser c 
   ON c.EmployeeNo = o.EmployeeNo 
WHERE c.EmployeeNo =  @EmployeeNo

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO [Phone]
    ( employeeno
    , PhoneNumber
    , CreatedBy
    , UpdatedBy
    )
   SELECT Ad.employeeno
        , Ad.PhoneNo
        , @CONTEXT_INFO
        , @CONTEXT_INFO 
     FROM ADUser AD 
    WHERE Ad.employeeno=@EmployeeNo

END

Open in new window

0
Sha1395
Asked:
Sha1395
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would guess a trigger on the table (which might be coded incorrectly) ?
because the UPDATE in this proc will only change the column value for the rows based on the WHERE + JOIN clause
0
 
kamindaCommented:
You query looks ok to me. Its either a data issue you are having in your tables or  a something else is running after this (may be a trigger as angelIII mentioned)
0
 
Sha1395Author Commented:
Thanks for your comment,is this anyway i can check or debug ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would first check if there is a trigger.
other than that, I would set up a test database with just the basic things to clearly identify when/what is happening
0
 
Sha1395Author Commented:
I will check the trigger and thanks again.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now