Trigger: who deleted what? 2

[nero]

Hi all,

A little help if possible:

I have created triggers to provide and audit trail of INSERTS, UPDATES and DELETES. No problem with this, I'm a bit rusty, but I picked it back up again after thumbing through my old uni books

I have a problem though: When a record is deleted, copy of the deleted record in the deleted table is placed in my audit table. GETDATE() gives me when it happend but to figure out who did it I'm left high and dry.

With Inserts and Updates my ColdFusion front end passes a session variable to SQL Server containing the username of the person committing the act, not so with delete.

My only solution is to perform an update of the record to be deleted to update the username field, then immediately delete it, thereby passing on the username.

The problem with this is for every delete, there is an update preceeding it.

Anyone have a better solution?

Thank you.

Nero

 

[rajeevnandanmishra]

Hi nero,
What you can do is in the trigger you can write something like this
------------------
insert in myAuditTable
SELECT column1, column2, column3,...,
SESSION_USER,getdate() FROM deleted
------------------
THIS session_user basically enter the current session user id into your audit table.

I hope this will move you into right direction.

 

[nero]

Thanks for that, all working now.