Inserted Table and Deleted Table On SQL Server

this is only a simple trigger and table to monitor the activity on an update activity to “members” table. it tracks the date when the update query is being executed and who execute it, it will be quite useful for a database that has many applications accessing it with different SQL User

Holding table :

CREATE TABLE [dbo].[MemberUpdateTracking](
	[MemberTrackingID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NULL,
	[BeforeMemberCode] [varchar](50) NULL,
	[BeforeFirstName] [varchar](255) NULL,
	[BeforeSurname] [varchar](255) NULL,
	[BeforeUserName] [varchar](255) NULL,
	[BeforePassword] [varchar](255) NULL,
	[BeforeEmailAddress] [varchar](255) NULL,
	[AfterMemberCode] [varchar](50) NULL,
	[AfterFirstName] [varchar](255) NULL,
	[AfterSurname] [varchar](255) NULL,
	[AfterUserName] [varchar](255) NULL,
	[AfterPassword] [varchar](255) NULL,
	[AfterEmailAddress] [varchar](255) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[SQLUser] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MemberUpdateTracking] PRIMARY KEY CLUSTERED
(
	[MemberTrackingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Triggers:

CREATE TRIGGER [dbo].[tr_Member_UPDATE]
	ON [dbo].[Members]
	FOR UPDATE
AS
BEGIN
	SET	NOCOUNT ON

	DECLARE @MemberID			int
	DECLARE @AfterFirstName		varchar(255)
	DECLARE @AfterSurname		varchar(255)
	DECLARE @AfterMemberCode	varchar(50)
	DECLARE @AfterUserName		varchar(255)
	DECLARE @AfterPassword		varchar(255)
	DECLARE @AfterEmailAddress	varchar(255)

	DECLARE @BeforeFirstName	varchar(255)
	DECLARE @BeforeSurname		varchar(255)
	DECLARE @BeforeMemberCode	varchar(50)
	DECLARE @BeforeUserName		varchar(255)
	DECLARE @BeforePassword		varchar(255)
	DECLARE @BeforeEmailAddress	varchar(255)


	--get information what will been inserted
	SELECT @MemberID = MemberID,
		   @AfterFirstName = FirstName,
		   @AfterSurname = Surname,
		   @AfterUserName = Username,
		   @AfterPassword = [Password],
		   @AfterMemberCode = MemberCode,
		   @AfterEmailAddress = EmailAddress
	FROM
		Inserted

	--only need for strange behaviour issue that we had before otherwise not necessary
	IF (@AfterFirstName = '' OR @AfterSurname = '' OR @AfterFirstName IS NULL OR @AfterSurname IS NULL)
	BEGIN
		--get the original information
		SELECT  @BeforeFirstName = FirstName,
				@BeforeSurname = Surname,
				@BeforeUserName = Username,
				@BeforePassword = [Password],
				@BeforeMemberCode = MemberCode,
				@BeforeEmailAddress = EmailAddress
		FROM
			Deleted

		--track the changes
		INSERT INTO MemberUpdateTracking(MemberID, BeforeMemberCode, BeforeFirstName,
					BeforeSurname, BeforeUserName, BeforePassword, BeforeEmailAddress,
					AfterMemberCode, AfterFirstName, AfterSurname, AfterUserName, AfterPassword,
					AfterEmailAddress, ModifiedDate, SQLUser)
		VALUES
			(@MemberID, @BeforeMemberCode, @BeforeFirstName, @BeforeSurname,
					@BeforeUserName, @BeforePassword, @BeforeEmailAddress, @AfterMemberCode,
					@AfterFirstName, @AfterSurname,
					@AfterUserName, @AfterPassword, @AfterEmailAddress,
					GETDATE(), SYSTEM_USER)
	END
END