I have inherited a SQL Server 2008 database to which calling applications have access through stored procedures.

Each table in the database has a shadow audit table into which Insert/Update/Delete operations for are logged.

Performance testing on populating the audit tables showed that inserting the audit records using OUTPUT clauses was 20% or so faster than using triggers, so this has been implemented in the stored procedures.

However, because this design cannot track changes made directly to the tables through DML statements issued directly against the tables, triggers have also been implemented which use the value of @@NESTLEVEL to determine whether or not to run the trigger (the assumption being that all DML run through stored procedures will have @@NESTLEVEL > 1). i.e. the body of the trigger code looks something like:

IF  @@NESTLEVEL =   1   -- implies call is direct sql so generate history from here
    BEGIN
... insert into audit table

This design is flawed because it won't track updates where DML statements are executed in dynamic SQL, or any other context where @@NESTLEVEL is raised above 1.

Can anyone suggest a completely reliable method we can use in the triggers to execute them only if not triggered by a stored procedure?

Or is this (as I suspect) not possible?