SQL Server - Client HostName in Networking Environment

To trigger event on specific client name at time of login, you need to create log on  trigger which is best solution for your requirement to track login from client host name.

https://msdn.microsoft.com/en-us/library/bb326598.aspx

Select allOpen in new window

I want to be able to track the client host name in the networking environment.

So, why did you open this question in SQL Server topic?

This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?

Hello,

You need to design architecture as below :


You can use ssis package to watch on table and execute your custom process.

This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?

Sorry but the Networking Environment is confusing me. Can you post the trigger code?

Here is the trigger code, every time an update is done from a client host in  a network environment or even locally when not using SSMS for update, the variable @pHostName comes empty.

ALTER TRIGGER [dbo].[contractlog] ON [dbo].[contract]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
      
      DECLARE @pTable VARCHAR(50)='CONTRACT';
      DECLARE @pPrimKey VARCHAR(50)='PKCONTRACT';
      DECLARE @pType CHAR(1);
      DECLARE @pHostName VARCHAR(50)=LEFT(HOST_NAME(),50);
      
      IF OBJECT_ID('tempdb..#tinserted') is not null DROP TABLE dbo.#tinserted;
      IF OBJECT_ID('tempdb..#tdeleted') is not null DROP TABLE dbo.#tdeleted;
      
      SELECT * INTO dbo.#tinserted FROM inserted;
      
      SELECT * INTO dbo.#tdeleted FROM deleted;            
            
      IF exists (SELECT * FROM inserted)
            IF exists (SELECT * FROM deleted)
                  SET @pType = 'U';                  
            ELSE
                  SET @pType = 'I';
      ELSE
            SET @pType = 'D';      
      
      EXEC dbo.trackchanges @pTable, @pPrimKey, @pType, @pHostName; /*Syntax tableName, primaryKeyField, TriggerType*/      
     
END

There's no reason to be empty inside the trigger.
What about the trackchanges procedure? How it is defined?

trackChanges is a stored procedure, and the @pHostName is one of the parameters sent to that stored procedure.
When I update the table from SSMS, the hostname comes with a value, while it is empty when the update is done from my application.

Host name should always exists since it's the name of the database server.
Or something is missing in the posted code or something very weird is happening. Unfortunaly I can't see what.