I want to take record of values updated in the main table everytime a user updates something
i want old and new updated value side by side on every update they do.
If at first they changed firstname the new and old values should be side by side and
next after saving it. if they update again it should insert new record to update log with updated firstname in firstname as we inserted earlier and with old and new lastname side by side on present update.
This is the code and tables im trying .
Any help for how can i achieve that.
What I have tried:
CREATE TABLE tbluser(
[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
[FirstName] [nvarchar](50) NULL,
[Lastname] [nvarchar](50) NULL,
[Contact] [bigint] NULL,
[Address] [nvarchar](250) NULL,
[Course] [nvarchar](50) NULL,
[DOJ] [datetime] NULL,
[Username] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[Timestamp] [datetime] NULL)
CREATE TABLE Updatelogs
(
[Id][int] NOT NULL,
[FIRSTNAME] [varchar](50) NULL,
[UFirstname] [varchar](50) NULL,
[LASTNAME] [varchar](50) NULL,
[ULASTNAME] [varchar](50) NULL,
[CONTACT] [bigint] NULL,
[UCONTACT] [bigint] NULL,
[ADDRESS] [varchar](50) NULL,
[UADDRESS] [varchar](50) NULL,
[COURSE] [varchar](50) NULL,
[DOJ] [date] NULL,
[USERNAME] [varchar](50) NULL,
[UpdUSERNAME] [varchar](50) NULL,
[PASSWORD] [varchar](50) NULL,
[UPASSWORD] [varchar](50) NULL,
[Timeofinsert] [datetime] NULL,
[ValueUpdated] [nvarchar](50) NULL
)
---------------------------------------------------------
CREATE TRIGGER tbluserUpdateloG on tbluser
FOR UPDATE
AS BEGIN
SET NOCOUNT ON;
Declare @Id int
DECLARE @FirstName nvarchar(50)
DECLARE @Lastname nvarchar(50)
DECLARE @Contact bigint
DECLARE @Address nvarchar(250)
DECLARE @UFirstName nvarchar(50)
DECLARE @ULastname nvarchar(50)
DECLARE @UContact bigint
DECLARE @UAddress nvarchar(250)
DECLARE @Course nvarchar(50)
DECLARE @DOJ datetime
DECLARE @Username nvarchar(50)
DECLARE @Password nvarchar(50)
DECLARE @UpdUsername nvarchar(50)
DECLARE @UPassword nvarchar(50)
DECLARE @Timestamp datetime
Declare @Timeofinsert varchar(50)
declare @action nvarchar(50)
select
@Id=inserted.Id,
@FirstName = inserted.FirstName ,
@Lastname = inserted.Lastname ,
@Contact = inserted.Contact ,
@Address = inserted.Address ,
@Course = inserted.Course ,
@DOJ = inserted.DOJ ,
@Username = inserted.Username,
@Password = inserted.Password ,
@Timestamp = inserted.timestamp from inserted
select * from Updatelogs where Id =@Id
IF @@ROWCOUNT <> 0
update Updatelogs
set UFirstname =@FirstName,
ULASTNAME =@Lastname ,
UCONTACT=@Contact ,
[UADDRESS]=@Address,
UpdUSERNAME=@Username,
[UPASSWORD]=@Password,
ValueUpdated='updated new item',
[Timeofinsert]= getdate()
where Id=@Id
else
insert into Updatelogs values(
@Id,
@FirstName,
@UFirstname,
@Lastname,
@ULASTNAME ,
@Contact,
@UCONTACT,
@Address,
@UADDRESS,
@Course,
getdate(),
@Username,
@UpdUSERNAME,
@Password,
@UPASSWORD,
GETDATE(),
'new record entry')
END