You're using Dynamic SQL (EXEC()). You need to create a mental image that Dynamic SQL is another batch. It has very little visibility into the stored procedure that is issue it. Variables within your stored procedure (@PKField) are not visible to the Dynamic SQL. The most common technique for communicating data back to the calling stored procedure is to write it to a temporary table. The Dynamic SQL writes the value to the temporary table and the calling stored procedure can read it from the table.

There is another issue with your stored procedure. A trigger is fired once in response to a change no matter how many rows are changed. Since the stored procedure is called from a trigger, it would need to be able to handle multiple rows. Yours can't. The Dynamic SQL will fail if there is more than one row in the Inserted or Deleted tables.

That said, I agree with Bob. Since this stored procedure basically boils down to a single INSERT statement, just build it into your triggers. This would also get around the problem of identifying the columns that you want to capture.

Just remember that mulitple rows can be affected.

-Mike