Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 23-Aug-22 6:51am
Comments
Richard Deeming 24-Aug-22 11:54am    
[Password] [nvarchar](50) NOT NULL

You're storing your users' passwords in plain text. I hope you've got extremely deep pockets - once your database is breached, you're going to be hit with an enormous fine for failing to protect your users' data correctly.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]
Randomuser787 24-Aug-22 12:14pm    
SURE will work on that thanks

Look at the documentation: MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples[^] it's pretty clear:
Quote:
Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
 
Share this answer
 
Comments
Randomuser787 19-Aug-22 6:07am    
i can see that i can use old and new .
should i give old and new for all the columns there is there any way that i can only take updated ones old and new in after update
OriginalGriff 19-Aug-22 6:37am    
Do you want to try askign that again, but in English this time? :D
Solved with
inserted as i and deleted d
 
Share this answer
 
Comments
Richard Deeming 24-Aug-22 11:52am    
If you're using Microsoft SQL Server, then why did you tag your question as "MySQL"?!

If you tag your question with the wrong DBMS, then you'll get answers based on that DBMS, and they won't work in the DBMS you're actually using.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900