x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

November 2006 Blog Posts

Generate Triggers for all Tables

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. Enjoy USE Northwind GO CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6)) GO 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   BEGIN  SELECT @sql = 'CREATE TRIGGER [' +...

posted @ Wednesday, November 29, 2006 9:49 AM | Feedback (0) | Filed Under [ Code Generataion ]

Hierarchies with a twist...rocks, no salt

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...

posted @ Monday, November 13, 2006 4:36 PM | Feedback (7) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET