I am creating a transaction log trigger for a table.
I would like to log the following data
The user login id: SYSTEM_USER
The User's Computer name: ?
The servers time and date: GetDate()
The trigger Action: Update, Delete, or Insert
The unique record ID for the affected table:
One column for the deleted row in xml raw:
One column for the inserted row in xml raw:
Is there a way to get the users computer name?
Consider this:
"select * from inserts for xml raw"
This is nice because i want to store the inserted and/or deleted table information as xml columns. But I would like to handle the transaction log in a way that created one transaction row add per row in the inserted or deleted table.
My end goal is to insert into the transaction log table as follows:
Lets say that my update trigger contains an inserted table with two rows and the deleted table would have the same, two rows.
I would like to insert two rows into my transaction log. with the username, date time, action and one column for the inserted row as xml raw, and one column for the deleted row as xml raw.
Anyone know how to do this?
It would be nice if i could impliment something like this:
'select * as xml raw from inserted' And it ould return as many rows as is in the inserted table, each row having one column wich represents that row in xml format.
'select * from inserted for xml raw' This is not good because it returns one row with one column whose value is an xml representation of the entire inserted table.
Thanx
Jerry Cicierega
The host_name() function will return this information, but it is not always set. Try looking this up in books online.|||
Thank you for responding. Yes this works on my system. The computer name part of my issue is solved.
The xml part of my question still stands.
Thank you !
