July 5, 2012 at 9:30 am
We have some new auditing requirements for our application where we need to log the user who deleted a record.
Currently we are using the MERGE command and passing in the logged in UserID as @user-id - since using the connection UserID will return the UserID of the application connecting to SQL, not the user who is deleting the record.
How have people handled the delete case? If I have a trigger - I cannot pass in UserID to log the correct user who is deleting the record. If I OUTPUT to a temp table and then INSERT using the @user-id - that works - but I no longer capture changes to the table via query analyzer or any other stored procedure.
What have people done to handle this aspect of the MERGE command - not use MERGE? 😉
Thanks,
Doug
July 5, 2012 at 10:39 am
You could try logging this with a DDL trigger or coding your own version of an audit by using the undocumented deleted table.
Or you could simply set up some operational data store that logs all data as it comes in / changes, and marks a column called deleted if the row is removed from the original database.
July 5, 2012 at 11:44 am
If it is the application user that you want, and you are not concerned about others accessing the database through SSMs or some other way, then just write to a table when the application deletes something. Pretty simple really...
Jared
CE - Microsoft
July 5, 2012 at 11:53 am
We have implemented by storing the application logged in user in the last modified user column for all the table which will be audited via a trigger to the audit database. In Audit database we will have the same table schema which will have change type column to record whether the record is INSERTed or UPDATEd or DELETEd.
Regards,
Ravi.
July 5, 2012 at 1:50 pm
Jared,
I was saying specifically through the MERGE statement, where you just DELETE.
Thanks for giving it a shot,
Doug
July 5, 2012 at 1:51 pm
Brandie,
Do you have a short example?
Thanks for the assist,
Doug
July 5, 2012 at 1:53 pm
Ravi,
I was saying specifically through the MERGE statement, where you just DELETE.
Thanks buddy,
Doug
July 5, 2012 at 1:55 pm
Ah. I believe you can use OUTPUT with a merge. I think I have a sample script, but I am having trouble finding it. I will post it here if I can dig it up.
Jared
CE - Microsoft
July 5, 2012 at 2:21 pm
Take a look at this:
MERGE tableName AS t
USING #tempTracking AS tt
ON t.keyColumn = tt.keyColumn
WHEN NOT MATCHED BY TARGET
THEN INSERT(keyColumn, columnA, columnB) VALUES(tt.keyColumn, tt.columnA, tt.columnB)
WHEN MATCHED AND (t.columnA <> tt.columnA OR t.columnB <> tt.columnB)
THEN UPDATE SET t.columnA = tt.columnA, t.columnb = tt.columnB
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT @login, $action, inserted.keyColumn, inserted.columnA, inserted.columnB, deleted.keyColumn, deleted.columnA, deleted.columnB
INTO someLogTable;
Jared
CE - Microsoft
July 5, 2012 at 2:57 pm
Spot on Jared - that was exactly what I had come up with - here's a snippet ( This was to handle the delete case, remember, the Insert and Update cases were working fine ):
MERGE dbo.FeaturePermission AS tgt
USING @tvp_Group AS src ON tgt.FeatureID = src.FeatureID AND tgt.PermissionGroupID = src.PermissionGroupID
WHEN MATCHED AND ( tgt.pCreate <> ISNULL(src.pCreate, 0) OR tgt.pRead <> ISNULL(src.pRead, 0) OR tgt.pUpdate <> ISNULL(src.pUpdate, 0) OR tgt.pDelete <> ISNULL(src.pDelete, 0) )
THEN
UPDATE SET
pCreate = src.pCreate,
pRead = src.pRead,
pUpdate = src.pUpdate,
pDelete = src.pDelete,
UpdateUserID = src.UserID
-- Test data to ensure it won't violate the table's check constraint
WHEN NOT MATCHED BY TARGET AND ( src.EmployeeID > 0 OR src.PermissionGroupID > 0 ) AND ( src.EmployeeID IS NULL OR src.PermissionGroupID IS NULL )
THEN
INSERT ( FeatureID, EmployeeID, PermissionGroupID, pCreate, pRead, pUpdate, pDelete, InsertUserID )
VALUES ( src.FeatureID, src.EmployeeID, src.PermissionGroupID, src.pCreate, src.pRead, src.pUpdate, src.pDelete, src.UserID )
WHEN NOT MATCHED BY SOURCE AND tgt.FeatureID = @GrpFeatureID AND tgt.PermissionGroupID IS NOT NULL
THEN
DELETE
OUTPUT $action AS [Action],
DELETED.[FeaturePermissionID] AS C1, DELETED.[FeatureID] AS C2, DELETED.[EmployeeID] AS C3, DELETED.[PermissionGroupID] AS C4, DELETED.[pCreate] AS C5, DELETED.[pRead] AS C6, DELETED.[pUpdate] AS C7, DELETED.[pDelete] AS C8, DELETED.[InsertDT] AS C9, DELETED.[InsertUserID] AS C10, DELETED.[UpdateDT] AS C11, DELETED.[UpdateUserID] AS C12
INTO @AuditTable;
END
INSERT INTO dbo.FeaturePermission_Audit( FeaturePermissionID, FeatureID, EmployeeID, PermissionGroupID, pCreate, pRead, pUpdate, pDelete, InsertDT, InsertUserID, UpdateDT, UpdateUserID, AuditDT, AuditUserID, Audit )
SELECT C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, GETDATE(), @userid, 'D' --CASE WHEN [Action] = 'Update' THEN 'U' ELSE 'D' END
FROM @AuditTable
WHERE [Action] = 'Delete' --IN ( 'Update', 'Delete' );
😀
July 6, 2012 at 4:22 am
Douglas Osborne-229812 (7/5/2012)
Brandie,Do you have a short example?
Not knowing which of my three suggestions you want an example for, I'll give you the trigger:
--Table is the user table you're logging. This trigger gets created on it.
--@polid is the table's PK (Identity column in my case)
--"u" means updated, "d" means deleted
CREATE trigger [dbo].[trMyTrigger]
on [dbo].[Table]
AFTER DELETE,UPDATE
as
SET NOCOUNT ON
DECLARE @xml xml,
@polid int,
@type char(1)
if (select count(*) from deleted) > 0
BEGIN
SELECT @polid=MyCol from deleted
SET @xml=(
SELECT *
FROM deleted
FOR XML RAW, ELEMENTS XSINIL
)
IF (select count(*) from inserted where MyCol=@polid) > 0
BEGIN
SET @type='u'
END
ELSE
BEGIN
SET @type='d'
END
INSERT INTO dbo.dba_TableLog (brecordinfo,blogtype)
SELECT @xml,@type
END
GO
Before you use this code, I advise researching it carefully. After all, you will be the one supporting it if something doesn't work properly, so you want to understand it as well as you can.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply