locked
MS SQL Delete Trigger session variable RRS feed

  • Question

  • Hi, Everyone.

    I want to keep the log detail for my record
    in MS SQL Database when user Delete a record.
    So I have a SQL Trigger Delete Function in related Table.
    What I want to keep is the User Name From My Software's User
    who delete the record, But i have no any field keep in the table,
    Is that MS SQL have any session variable or temp variable that
    can let my system to update and run at once together with Trigger Delete and
    so keep in the log detail table ?

    My Program Current Login Username, I have keep it Database,
    But The problem is :-
    1> There are multi-user login at a time.
    2> During Trigger Delete, username that delete not send to trigger.
    So, What i want to know is, how or any other way to Send / communicate
    with Trigger Delete when a user delete a record. Thank in advanced ...

    Tuesday, May 15, 2012 8:17 AM

Answers

  • Hi,

    Here it is (without an indexes), for me it works like a charm:

    SET NOCOUNT ON;
    
    -- Create and populate user table.
    CREATE TABLE dbo.Users
        (CTI varbinary(128) NOT NULL PRIMARY KEY
        ,username nvarchar(100));
    INSERT INTO dbo.Users (CTI, username)
    VALUES (0x222, N'Olaf Helper');
    INSERT INTO dbo.Users (CTI, username)
    VALUES (0x345, N'Sheng2000');
    
    -- Create and populate data table
    CREATE TABLE dbo.myData
        (ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY
        ,datacol nvarchar(100));
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 1');
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 2');
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 3');
    
    -- Create log table for the data
    CREATE TABLE dbo.myData_LOG
        (ID int NOT NULL
        ,datacol nvarchar(100)
        ,username nvarchar(100));
    GO
    
    -- Trigger to log deletes
    CREATE TRIGGER dbo.TRD_myData ON dbo.myData
    FOR DELETE
    AS
    BEGIN
        DECLARE @userName nvarchar(100);
    
        SET @userName = (SELECT username FROM dbo.Users 
                         WHERE CTI = CONTEXT_INFO());
    
        INSERT INTO dbo.myData_LOG (ID, datacol, username)
        SELECT D.ID
              ,D.datacol
              ,@userName
        FROM deleted AS D;
    END
    GO
    
    -- Now "I" delete a record set
    SET CONTEXT_INFO 0x222; -- = Olaf
    DELETE FROM dbo.myData
    WHERE ID = 1;
    
    -- Now "Sheng2000" delete a record set
    SET CONTEXT_INFO 0x345; -- = Sheng2000
    DELETE FROM dbo.myData
    WHERE ID = 2;
    
    -- Select the result
    SELECT *
    FROM dbo.myData_LOG;
    
    -- DROP TABLE dbo.Users;
    -- DROP TABLE dbo.myData;
    -- DROP TABLE dbo.myData_LOG;
    
    


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Tuesday, May 22, 2012 7:07 AM
    Friday, May 18, 2012 1:21 PM

All replies

  • Hello,

    You can use the build-in function SUSER_NAME() to get the name of the current user:

    SELECT suser_name()


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, May 15, 2012 9:21 AM
  • hi, Olaf Helper

    My Program Current Login Username (This Is Program's Login User Name not the MS SQL Login User Name) , I have keep it Database,
    But The problem is :-
    1> There are multi-user login at a time.
    2> During Trigger Delete, username that delete not send to trigger.
    So, What i want to know is, how or any other way to Send / communicate
    with Trigger Delete when a user delete a record. Thank in advanced ...

    Tuesday, May 15, 2012 9:54 AM
  • 1> There are multi-user login at a time.
    2> During Trigger Delete, username that delete not send to trigger.

    Yes, I understood this and as I wrote, SUSER_NAME give you the login name of the current user how delete the data, also in a multi-user enviroment, of course.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, May 15, 2012 9:59 AM
  • But the Delete Query Is create From My V.B Program not from MS SQL Server Management Studio Express. In this case,

    this command still work ? ... Thanks

    Tuesday, May 15, 2012 10:24 AM
  • It works, if the app login with the windows authentification of the current user.

    If you use one fix SQL login for all users, then it won't work.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, May 15, 2012 10:50 AM
  • Yes, I use same SQL Login for all users. and login username is control

    by my VB problem. Is there any idea to send a variable (Means My Program login User name as a variable)

    To Run Palleral with Trigger Delete ? Thanks ...

    Wednesday, May 16, 2012 4:22 AM
  • Ok, an other option is to use a CONTEXT_INFO, but this is a binary value, you have to create a solution to map the binary to a user name; e.g. a table with all user names having a unique binary id.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, May 16, 2012 5:43 AM
  • ok, if using CONTEXT_INFO to store Login User binary id.

    What is more than 1 user delete from different module (Eg: 1 delete Customer, 1 delete Vendor) From Different Terminal.

    Will that CONTEXT_INFO overwrite by 2nd user binary id before 1st User trigger delete is fire ?

    Wednesday, May 16, 2012 7:06 AM
  • Every transaction fires the trigger separatly, means if two users delete at "the same time", then the trigger fires two times and in the trigger you get the CONTEXT_INFO of the user who fired the trigger.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, May 16, 2012 10:54 AM
  • Hi, Olaf Helper. For My situation, Can you help me in write some code that send the username's id to CONTEXT_INFO,

    I really no idea in create this ... thanks.

    Thursday, May 17, 2012 2:04 AM
  • Hi,

    Here it is (without an indexes), for me it works like a charm:

    SET NOCOUNT ON;
    
    -- Create and populate user table.
    CREATE TABLE dbo.Users
        (CTI varbinary(128) NOT NULL PRIMARY KEY
        ,username nvarchar(100));
    INSERT INTO dbo.Users (CTI, username)
    VALUES (0x222, N'Olaf Helper');
    INSERT INTO dbo.Users (CTI, username)
    VALUES (0x345, N'Sheng2000');
    
    -- Create and populate data table
    CREATE TABLE dbo.myData
        (ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY
        ,datacol nvarchar(100));
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 1');
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 2');
    INSERT INTO dbo.myData (datacol)
    VALUES (N'Test 3');
    
    -- Create log table for the data
    CREATE TABLE dbo.myData_LOG
        (ID int NOT NULL
        ,datacol nvarchar(100)
        ,username nvarchar(100));
    GO
    
    -- Trigger to log deletes
    CREATE TRIGGER dbo.TRD_myData ON dbo.myData
    FOR DELETE
    AS
    BEGIN
        DECLARE @userName nvarchar(100);
    
        SET @userName = (SELECT username FROM dbo.Users 
                         WHERE CTI = CONTEXT_INFO());
    
        INSERT INTO dbo.myData_LOG (ID, datacol, username)
        SELECT D.ID
              ,D.datacol
              ,@userName
        FROM deleted AS D;
    END
    GO
    
    -- Now "I" delete a record set
    SET CONTEXT_INFO 0x222; -- = Olaf
    DELETE FROM dbo.myData
    WHERE ID = 1;
    
    -- Now "Sheng2000" delete a record set
    SET CONTEXT_INFO 0x345; -- = Sheng2000
    DELETE FROM dbo.myData
    WHERE ID = 2;
    
    -- Select the result
    SELECT *
    FROM dbo.myData_LOG;
    
    -- DROP TABLE dbo.Users;
    -- DROP TABLE dbo.myData;
    -- DROP TABLE dbo.myData_LOG;
    
    


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Tuesday, May 22, 2012 7:07 AM
    Friday, May 18, 2012 1:21 PM