Trigger-based SQL Server auditing

Question

I'm working on a simple VB NET/WPF application that communicates with a SQL Server 2012 database, which at least two or more people could be using at the same time.

To keep track of any and every change in the database (INSERT/UPDATE/DELETE), I'm implementing a simple trigger-based auditing system, where obviously, any data getting updated/deleted/inserted is saved in its corresponding auditing table, while also keeping track of the user and the date/time of the operation.

I obviously found tons of guides while searching. But I noticed that most of these SQL Server guides would use JOINs in their queries to extract the data stored in the Inserted or Deleted tables, while I achieved the same result without any JOINs.

Exemple :

• My INSERT trigger :

  CREATE TRIGGER [HumanResources].[after_insert_humanresources_shift]
  ON [HumanResources].[Shift]
  AFTER INSERT
  AS
  BEGIN
  INSERT INTO [HumanResources].[Shift_Audit] 
  (
      -- [EventID], [EventBy] and [EventOn] have autovalues
      [EventType],
      [ShiftID],
      [Name],
      [StartTime],
      [EndTime],
      [ModifiedDate]
  )   
  SELECT 
       'INSERT',
      [ShiftID], 
      [Name], 
      [StartTime], 
      [EndTime], 
      [ModifiedDate]
  FROM 
      [Inserted]
  END
  

• This guide's INSERT Trigger :

  create trigger tblTriggerAuditRecord on tblOrders
  after insert
  as
  begin
    insert into tblOrdersAudit 
    (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
    select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
    from  tblOrders t 
    inner join inserted i on t.OrderID=i.OrderID 
  end
  

My DELETE and UPDATE triggers do exactly the same thing. And all these work for both single D/U/I queries or multiple ones (multiple Updates in a single query for instance). Is there a specific reason why I should be using JOINs ?

Answer 1

Yes the "inner join inserted i on t.OrderID=i.OrderID" checks for dependencies on orders table, and will insert only records where there are genuine orders in the orders table, inserting to independent tables does not test the data for dependencies at entity level. hope that helps.