Table history trigger in SQL Server?

Question

I would like to create a trigger that writes to the history table with inserted values ​​both before and after update values. I would also like to include as much information as possible about the account that is performing the upgrade. How to include account information in your trigger?

Here is what I still have:

CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', '???'
FROM deleted

INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'AFTER UPDATE', '???'
FROM inserted

What should I put in place of "???"?

Answer 1

, SYSTEM_USER . , - , -, , Update:

CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', inserted.userId
FROM MyTable
    Join inserted
        On inserted.id = MyTable.id

INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'AFTER UPDATE', userId
FROM inserted

Answer 2

FWIW, SQL Server 2008, , . .

Answer 3

ORIGINAL_LOGIN()

you can see a further definition of what he is doing here SQL Server - current username