Trigger to find who is updating data on a table and when (Audit Table)

Trigger to find who is updating data on a table and when (Audit Table)
Test thoroughly and Implement in Production


Create the table :
CREATE TABLE AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME VARCHAR(100),
HOST_NAME Varchar(128),
nt_username Varchar(128),
APP_NAME Varchar(128),
program_name  VARCHAR(128),
cmd Varchar(MAX),
net_address VARCHAR(128),
MODIFIED_BY Varchar(128),
MODIFIED_DATE DATETIME
)
GO




CREATE  TRIGGER [dbo].[tr_d_AUDIT_AccountStatus]
ON [dbo].[AccountStatus]
FOR UPDATE, INSERT -----AFTER INSERT,UPDATE

AS

BEGIN
    DECLARE
        @IDENTITY_SAVE              varchar(50),
        @AUDIT_LOG_TRANSACTION_ID       Int,
        @PRIM_KEY               nvarchar(4000),
        @ROWS_COUNT             int

    SET NOCOUNT ON


    Select @ROWS_COUNT=count(*) from Inserted
    Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

    INSERT   INTO dbo.AUDIT_LOG_TRANSACTIONS
    (   TABLE_NAME,
        [HOST_NAME],
nt_username,
[APP_NAME],
[program_name],
cmd,
net_address,
        MODIFIED_BY,
        MODIFIED_DATE
    )
        SELECT  [TableName] = OBJECT_NAME([parent_obj]) ,
                CASE WHEN LEN(HOST_NAME()) < 1 THEN ' '
                     ELSE HOST_NAME()
                END AS HostName ,
                ( SELECT TOP 1
                            nt_username
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                CASE WHEN LEN(APP_NAME()) < 1 THEN ' '
                     ELSE APP_NAME()
                END ,
                ( SELECT TOP 1
                            program_name
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                ( SELECT TOP 1
                            cmd
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                ( SELECT TOP 1
                            net_address
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                SUSER_SNAME() ,
                GETDATE()
        FROM    sysobjects
        WHERE   [xtype] = 'tr'
                AND [name] = OBJECT_NAME(@@PROCID);


END

go


to see the Audit results after some time;
SELECT * FROM AUDIT_LOG_TRANSACTIONS