Read https://stackoverflow.com/questions/10060408/sql-server-2008-change-data-capture-vs-triggers-in-audit-trail/35455760#35455760 and https://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server to get an idea of your options.
If you decide to go for the trigger solution, make sure INSERTs, UPDATEs and DELETEs of multiple records in one transaction get logged too. The trigger you provided will only keep track of one change, no matter how many recods were inserted. You need to replace it by something like:
INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate()
FROM inserted AS i
As your trigger gets fired after an INSERT or an UPDATE or a DELETE, your trigger must first determine what action activated it:
--find oud if the event that activated the trigger was an INSERT, an UPDATE, a DELETE or got activated for nothing (like by UPDATE table_name SET col1 = 'Hello' WHERE id < 0, when all ID's are positive integers).
DECLARE @Action as char(6);
SET @Action = CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'UPDATE'
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'INSERT'
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'DELETE'
ELSE 'NUTIN'
END
followed by
if @action = 'INSERT'
BEGIN
INSERT INTO TableTestTriggers_Audit_INSERTS (TableTestTriggersID, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate()
FROM inserted AS i
END
if @action = 'UPDATE'
BEGIN
INSERT INTO TableTestTriggers_Audit_UPDATES (TableTestTriggersID, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate()
FROM inserted AS i
END
if @action = 'DELETE'
BEGIN
INSERT INTO TableTestTriggers_Audit_DELETES (TableTestTriggersID, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT D.ID, D.Row1, D.Row2, D.Row3, D.Name, D.EnterDate, GetDate()
FROM DELETED as D
END
In case you want to go for one audit table:
if @action IN ('INSERT', 'UPDATE')
BEGIN
INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Action, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT i.ID, @action, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate()
FROM inserted AS i
END
if @action = 'DELETE'
BEGIN
INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Action, Row1, Row2, Row3
, Name, EnterDate, AuditDate)
SELECT D.ID, @action, D.Row1, D.Row2, D.Row3, D.Name, D.EnterDate, GetDate()
FROM DELETED as D
END
how could I capture who they are logged in as.
Best is to let the application capure that and write it to the table, so you can use
that in the audit table too.