How to include the clients ip adress in to an audit as default constrain in sqlserver.

Question

Am writing av analytical standard solution register from the doping laboratory that i work for and got stuck on the problem how to get the ipaddress of the client in to the audit table.

I have found a straight forward method for actually get the ipaddress on the web and my question is not about that.

I got triggers on every table inserting records in to the audit table and I do not want to wright the inserting of the ipaddress manualy in every trigger. I would like to have something like a DEFAULT Constraint do the actual insertion of the ipaddress, but when I try I get errors about sub Queries not allowed.

Here is the way to get the address,

SELECT     client_net_address
FROM       sys.dm_exec_connections
WHERE     (session_id = @@SPID)  

Answer 1

The documentation specifies that a DEFAULT constraint can be "a constant, NULL, or a system function". Since there is no system function that returns the net address of the client, you cannot do what you want directly.

A trigger is the obvious solution here. Audit triggers are often created and maintained automatically from a template anyway, because they are usually identical on all tables, and if you aren't doing this already then perhaps this would be a good opportunity to start. That would avoid your issue with manually adding the code everywhere.