Newb with a serious Trigger question.

Ok I am stumped. I am a newbie in terms of triggers and procedures and am
used to writing inline sql statements , so here is my situation.
I have an audit table for all the updates , inserts and deletes throughout
one of my application database(SQL2005).
Whenever something happens a record gets inserted into this table(this was
all accomplished by an external dll coded somewhere else that i found on the
internet - and all this works fantastically.)
The problem i have is that after the insert into the audit table occurs, i
want to be able to do this...
have a second trigger for the insert on the audit table
this will go out using the Primary key of the edited table (eg. tasks) and
the table name (eg tasks_) and operation type (eg UPDATE< INSERT whatever)
and get these values that are now in the new audit table record.
then i want to use these values to dynamically select a created_By field
from say the tasks table and update the audit record with this new
created_By field.
the theory is that the dll works but puts dbo in for the user all the time,
so i want to go into the application side and grab the user who entered the
info and update the audit trail records.
Is this clear and concise or am i mumbling?
Any help would be vastly appreciated.
Thanks in advance,
COlinColin Smart (csmart@.nf.sympatico.ca) writes:
> I have an audit table for all the updates , inserts and deletes throughout
> one of my application database(SQL2005).
> Whenever something happens a record gets inserted into this table(this was
> all accomplished by an external dll coded somewhere else that i found on
> the internet - and all this works fantastically.)
> The problem i have is that after the insert into the audit table occurs, i
> want to be able to do this...
> have a second trigger for the insert on the audit table this will go out
> using the Primary key of the edited table (eg. tasks) and the table name
> (eg tasks_) and operation type (eg UPDATE< INSERT whatever) and get
> these values that are now in the new audit table record.
> then i want to use these values to dynamically select a created_By field
> from say the tasks table and update the audit record with this new
> created_By field.
> the theory is that the dll works but puts dbo in for the user all the
> time, so i want to go into the application side and grab the user who
> entered the info and update the audit trail records.
From exactly where is this DLL invoked?
This sounds like a solution for SQL 2000 or earlier. That trigger
would be messy to code. It sounds as if the DLL runs as dbo, in
which case you could use dynamic SQL. But if any of the tables does
not have a Created_by column, it will bloe up at run-time. A better
solution would be to pass the DLL the value of original_login() somehow.
(On SQL 2005 you should use original_login(), rather than SYSTEM_USER.)
On SQL 2005, the xml column can be very good for auditing. It depends
exactly what you will use the audit data for, but if you are not going
to run any searches on other data than the table name and key values,
you can use an xml column. The nice thing is that you can do a
SELECT ... FROM inserted FOR XML, and the triggers can be quite generic.
Even nicer is that you can make an entire generic program that reads
the log table, and presents the differences between two log records.
If you insert directly into the auditlog, there is not any problem of
retrieving the current user.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx