What did YOU do!  I lost All MY Work!

Sound familiar?  I always love it when they blame the database.  User or Developer, it doesn't matter, you and the database will ALWAYS be the first one to be blamed.  Facts/smacts, they lost their data, and it's all your fault.

BUT!

When you hit them square between the eyes that either the manager themself, or and underling destroyed a weeks worth of work....well there's just that great feeling when you see it in their face the time the realization hits home.....knida like when an 80 ton diesel engineer and a VW bug dance...talk about punch buggy....(where did that term come from?)

Anyway...caveats....we all get caveats...

1st, I added code not not log a mass delete, except
for 1 row in the log...you could take it out, and have all of the data recoverable

2nd, and this is bizzare, you'll notice that it says it can't find the second trigger...and if you design the table in EM, you'll see the dbo trigger, not the x002548 one.  Anyone know why? BUT it does work. The testing at the end proves it. 

Another triumph of the sql server catalog

Just cut and paste the code in to Query Analyzer and excecute it.

Should run no problem. Use Grid mode.

USE Northwind
SET NOCOUNT ON
GO

-- For example purposes, create a sample tables
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
CREATE TABLE x002548.myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
GO

-- Give the sample tables some data
INSERT INTO myTable99(Col2, Col4)
     SELECT 'x', 1 UNION ALL SELECT 'y', 2 UNION ALL SELECT 'z', 3
INSERT INTO x002548.myTable99(Col2, Col4)
     SELECT 'a', 24 UNION ALL SELECT 'b', 25 UNION ALL SELECT 'c', 26

--Lets have a look
SELECT 'x002548' AS Source, * FROM x002548.myTable99
UNION ALL
SELECT 'dbo' AS Source, * FROM myTable99
GO

-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO

-- Populate the audit driver table with the table you want...you can use any type of process for this
INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
     SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
       FROM INFORMATION_SCHEMA.Tables
      WHERE TABLE_NAME LIKE 'myTable99%'

-- Lets create some audit tables based on the Driver

DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
 SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' +  @TABLE_NAME + '_H ('
 + '  HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
 + ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
 + ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
 , @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME
  + ' ' + DATA_TYPE
  + CASE  WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
   WHEN DATA_TYPE = 'decimal'    THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
            + CONVERT(varchar(20),NUMERIC_SCALE) + ')'
          ELSE ''
    END
   FROM INFORMATION_SCHEMA.Columns
  WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
 ORDER BY ORDINAL_POSITION

 SELECT @SQL = @SQL + @COLUMN_NAMES + ')'
 
 EXEC(@SQL)

 SELECT @SQL = '', @COLUMN_NAMES = ''

 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
 
  END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

SELECT *
  FROM INFORMATION_SCHEMA.Columns
 WHERE TABLE_NAME Like 'myTable99%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

-- Now Lets create the audit TRIGGERS

DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
 SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME
   FROM INFORMATION_SCHEMA.Columns
  WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
 ORDER BY ORDINAL_POSITION

 SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' +  @TABLE_NAME + '_TR ON ' +  @TABLE_SCHEMA + '.' +@TABLE_NAME
 + ' FOR UPDATE, DELETE AS '
 + ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
 + ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
 + ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
 + '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
 + ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT  ''U'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 + ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC = '''' '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT    ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 + ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC <> '''' '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT  TOP 1  ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 
 EXEC(@SQL)

 SELECT @SQL = '', @COLUMN_NAMES = ''

 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
 
  END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

-- Now let's pretend to be an ignorant boss...not to much of a stretch there....

UPDATE myTable99 SET Col2 = 'G'

SELECT * FROM myTable99_H

DELETE FROM myTable99 WHERE Col1 = 1

SELECT * FROM myTable99_H

DELETE FROM x002548.myTable99

SELECT * FROM x002548.myTable99_H

SELECT * FROM myTable99

SELECT * FROM x002548.myTable99
GO
 
SET NOCOUNT OFF
DROP TABLE myAudit99
DROP TRIGGER dbo_myTable99_TR
DROP TRIGGER x002548_myTable99_TR
DROP TABLE dbo.myTable99
DROP TABLE x002548.myTable99
DROP TABLE dbo.myTable99_H
DROP TABLE x002548.myTable99_H
GO