Audit triggers problem

Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.

The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.

This is the trigger i have been using - could someone tell me how to modify it to work.

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @.TableName to match the table to be audit trailed
*/

ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as

declare @.bit int ,
@.field int ,
@.maxfield int ,
@.char int ,
@.fieldname varchar(128) ,
@.TableName varchar(128) ,
@.PKCols varchar(1000) ,
@.sql varchar(2000),
@.UpdateDate varchar(21) ,
@.Action nvarchar(50) ,
@.HostName nvarchar(50),
@.PKFieldName varchar (1000)


IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @.Action = 'UPDATE'
SELECT @.DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE

--insert = inserted contains data, deleted does not
BEGIN
SET @.Action = 'INSERT'
select @.DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @.Action = 'DELETE'
select @.DeviceID = (SELECT DeviceID from deleted)
END

select @.TableName = 'TableName'

-- date
select @.HostName = host_name(),
@.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@.DeviceID,
@.PKFieldName=(select top 1 c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @.PKCols = coalesce(@.PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @.PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @.TableName)
return
end

select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION > @.field
select @.bit = (@.field - 1 )% 8 + 1
select @.bit = power(2,@.bit - 1)
select @.char = ((@.field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0
begin
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION = @.field
select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @.sql = @.sql + ' select ''' + @.TableName + ''''
select @.sql = @.sql + ',''' + @.fieldname + ''''
select @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
select @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
select @.sql = @.sql + ',''' + @.UpdateDate + ''''
select @.sql = @.sql + ',''' + @.Action + ''''
select @.sql = @.sql + ',''' + @.HostName + ''''
select @.sql = @.sql + ',''' + @.PKFieldName + ''''
select @.sql = @.sql + ' from #ins i full outer join #del d'
select @.sql = @.sql + @.PKCols
select @.sql = @.sql + ' where i.' + @.fieldname + ' <> d.' + @.fieldname
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is null and d.' + @.fieldname + ' is not null)'
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is not null and d.' + @.fieldname + ' is null)'

exec (@.sql)
end
endYou have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...|||You have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...

How do 'move the whole row to history' - im not sure.

The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE, DELETE
AS
INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
SELECT Col1, Col2, ModifiedDate FROM deleted
GO

INSERT INTO myTable99(Col2)
SELECT 'x'
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myAudit99
GO

Attach/Detach versus Restore/Backup

Hello All,
Given a choice between attach/detach and restore/back up a database(and
subsequently deleting the database) , which one is faster? What I am
interested is, which of the above will release all the memory and other
resources.
Thanks in advance.
Regards,
SoumitraDefine releasing memory and other resources. Memory is
controlled at the server level.
If you delete (nee drop) the DB, you're just deleting it.
Detach does that - it detaches it, but kinda expects to be
re-attached after, say, copying the file. So if your goal
is to delete, I would do a full normal BACKUP and then
delete.
There are also differences in the process: for example, if
using RESTORE, you get your statistics rebuilt.
I would not define things in terms of speed per se if this
is a mission-critical DB you need to do something with.
Speed is certainly a factor, but being down because you
did the wrong thing possibly will negate any speed
differential.
>--Original Message--
>Hello All,
>Given a choice between attach/detach and restore/back up
a database(and
>subsequently deleting the database) , which one is
faster? What I am
>interested is, which of the above will release all the
memory and other
>resources.
>Thanks in advance.
>Regards,
>Soumitra
>
>.
>|||Backup/Restore advantages:
1. Backup you can do on-line while Detach is off-line only operation
2. Backup will create file which has less size than original DB
3. Backup will contain the whole DB (DB+Log) while with Detach you will have
separate file for DB and Log.
4. With backup you can check integrity to be sure file iz not corrupted
after actual backup.
Detach advantages:
1. It is faster to move DB with Detach than with backup - you will need just
copy files and don't need to spend a time on a backup operation.
2. The files will contain current DB and current Transaction log while in
case of full backup you will loose current Transaction Log - it is important
in case of emergency situation with replication.
Regards.
---
All information provided above AS IS.
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Thanks Guys.
One question that remains is how can one tell SQL Server to release the
memory it holds onto? Let's say I create 1000 databases in a single instance
of SQl Server/MSDE. At the end of this the memory usage let say is 200 MB.
Now out of the 1000 databases, the first 100 databases are not used and
there is no user connected to it.
Is there any way to get back the momory associated with the first 100
databases?
Thanks.
Regards,
Soumitra
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Hi Soumitra
You just drop the databases which are not in use. For
precautionary steps , just have backup of the databases
which you will be DROPING , move these backup files to
Tape or some secured place .
Regards
Suri
>--Original Message--
>Thanks Guys.
>One question that remains is how can one tell SQL Server
to release the
>memory it holds onto? Let's say I create 1000 databases
in a single instance
>of SQl Server/MSDE. At the end of this the memory usage
let say is 200 MB.
>Now out of the 1000 databases, the first 100 databases
are not used and
>there is no user connected to it.
>Is there any way to get back the momory associated with
the first 100
>databases?
>Thanks.
>Regards,
>Soumitra
>"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote
in message
>news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
>> Hello All,
>> Given a choice between attach/detach and restore/back
up a database(and
>> subsequently deleting the database) , which one is
faster? What I am
>> interested is, which of the above will release all the
memory and other
>> resources.
>> Thanks in advance.
>> Regards,
>> Soumitra
>>
>
>.
>|||Soumitra
Why are you so worried about releasing the memory? SQL
Server will use all the memory it needs from the amount
that is available . It will release it if another process
requires it, so usually it is not a problem. It can
sometimes be unpredictable when sharing a server with non
SQL Server applications, so it is best practice to not run
anything but SQL Server on your server.
If you are running another application and are having
memory problems you can always set SQL Server to use fixed
memory. You may slow the performance of your SQL Server by
doing this.
Hope this helps.
John|||"Allan Hirt" <allanh@.avanade.com> wrote in message
news:02f301c3a7e2$5dab22d0$a601280a@.phx.gbl...
> Detach does that - it detaches it, but kinda expects to be
> re-attached after, say, copying the file. So if your goal
> is to delete, I would do a full normal BACKUP and then
> delete.
In what way does it "expect" to be re-attached?
References to the detached database are removed. There is no expectancy.
Never mind the theological debate about whether or not a piece of software
CAN expect something ;)