January 21, 2010 at 8:50 am
I have a table where data is being insered by various processes. I would like to create an audit log where I can see the details of the process that is doing the insertion. Ie is it a stored procedure, who is running it, from which hostpc etc.
I am aware that I need to do this via a trigger but what can i use to provide the above information ?
Any help would be great. Google is not my friend today !
January 21, 2010 at 9:45 am
MIght I suggest that you examine using the OUTPUT clause, which is much easier to code for multiple rows being insterted/updated/deleted than a trigger.
Use Books On Line it has a multitude of sample code to help you write the T-SQL that you would need.
Here is an example from BOL -- it uses a table variable but I have used similiar code to write to either a temp table, or a table in the database so I can review the data over a longer period of time.
USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800 and 810;
--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID
FROM @MyTableVar;
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply