Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: Get information about who perfomed transaction?

    I have written a Trigger to log deletions in another table, usual simple stuff. But I was wondering if it's possible to get any further information about the transaction, who did it for instance.

    Any help greatly appreciated.

    Here is my simple script.

    Code:
    CREATE TABLE dev_SD_triggertesting_log_20091201 (
    	delID				int identity (1,1)
    ,	DateDel			datetime DEFAULT getdate()
    ,	memberid_			int
    ,	datejoined_			datetime
    ,	list_				varchar(100)
    ,	MemberType_		varchar(20)
    ,	univadis_id			varchar(100)
    ,	univadis_first_name      varchar(100)
    )
    
    GO
    CREATE TRIGGER tr_log_deletion
    ON dev_SD_triggertesting_20091201
    FOR DELETE
    AS
    INSERT INTO dev_SD_triggertesting_log_20091201 (
    	memberid_
    ,	datejoined_
    ,	list_
    ,	MemberType_
    ,	univadis_id
    ,	univadis_first_name
    )
    SELECT 
    	memberid_
    ,	datejoined_
    ,	list_
    ,	MemberType_
    ,	univadis_id
    ,	univadis_first_name
    FROM deleted

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you use windows authentication: SUSER_NAME (Transact-SQL)
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    What if we're using SQL authentication?
    Also are there ways of getting any more information, say from the deleted table?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you use SQL auth, then who did it is <the SQL login>...


    deleted virtual table only contains columns from the actual table.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    Ok, thanks. I was just wondering if there was a "magic" way to get more attributes from the deleted table, eg time it was created, number of records etc. More important was getting who deleted the records, so thanks for that.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming you don't use sproc only access to your database?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •