Well, I did this originally to generate triggers for all tables in a database to audit data changes, and that is simple enough, just move the entire row from the deleted table to a mirrored audit table.
But someone wanted to track activity on tables, so it's a little more simple. Here we create one log table, and any time a dml operation occurs, it is written there.
CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6))
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHILE @TABLE_NAME IS NOT NULL
SELECT @sql = 'CREATE TRIGGER [' +...
EDIT: The script has been repaired and paired down.
Basically this "solution" assigns a derived value to each entity...which I called codex for lack of better term. Each Child inherits their Parents Codex node signature. In the code below I show how to add a new position in the tree and how to move an entire branch...not sure what else you would want to do, but if you let me know, I'll take a crack at. It also shows how to "mine" different element of meta data about the tree.
This is a link that launched this discussion
Ever wanted to do set...