Audit Trigger on Row with Text Column
-
I am very new to SQL, so forgive me if this is a dumb question.
I am trying to write a simple trigger to copy the entire row from one table to an audit table on update and delete. Someone on usenet gave me a great sample which worked, but some of my tables have text columns in which also need auditing.
It has been suggested that I use an INSTEAD OF trigger to do this auditing, but I don't know how to use this.
I also have a couple of additional columns in my audit tables (to insert into what the action was and who did it - some of which is going to be supplied from parameters if this is possible). For instance, I was going to have my app (ASP) prompt the user for a reason for modifiying a row when they tried to make teh change, then insert this reason into the audit table along with the original record.
Can someone please post some simple sample of how I could do this?
I was going to do the auditing from my ASP app I am building but thought it would be much better to do it as a trigger on SQL (much less traffic etc.).
-
This was removed by the editor as SPAM
-
I would equally be interested on any thoughts with regard to auditing Text Fields and how people approach it
-
OK Sample:
create table YourBaseTable (
pkey int primary key,
col text
)
go
create trigger InsteadOf_Trigger_Example on YourBaseTable
instead of update
as
if update(pkey)
begin
print 'Update of primary key is not allowed'
return
end
-- select pkey, Col as old from deleted
-- select pkey, Col as new from inserted
update YourBaseTable set col = inserted.col
from inserted
where YourBaseTable .pkey = inserted.pkey
if @@error <> 0
begin
-- Use this spot to update you audit Table
end
go
-- be aware that you may want to use the after trigger for the other columns if you need that sepratedly
exec('update YourBaseTable set Col = '''+ replicate('data1',8000) + replicate('data2',8000)+'''')
select pkey, datalength(Col) as length_of_col
from YourBaseTable
If you can restrict the access to to the table through sps you may avoid the triggers altogether but it is just a design decision
Cheers,
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply