Audit Trigger
David Lozzi
Here's the deal. I know I can create a trigger to insert all of the fields
into another table that is the same structure as the original table. What I
want to do is loop through all of the fields (40ish) and track only the
fields that have changed then store that information in a table, as seen
below
CREATE TABLE [dbo].[tblAudit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[dtDateAdded] [datetime] NOT NULL CONSTRAINT [DF_tblAudit_dtDateAdded]
DEFAULT (getdate()),
[strUserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strField] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strOldData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strNewData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
So, is there a way to loop through the columns of a table in TSQL and then
compare them. I can think it through in VB easy enough, don't know abou
TSQL.
Thanks!
--
David Lozzi
Product Development Manager
Delphi Technology Solutions
www.delphi-ts.com - www.lozzi.net
David Lozzi
fields. Is there another way?
DECLARE @vOld as varchar(1000)
DECLARE @vNew as varchar(1000)
DECLARE @ModBy as varchar(100)
DECLARE @ID as int
SELECT @ModBy = strLastModifiedBy FROM inserted
SELECT @ID = ID from inserted
SELECT @vNew = intEntity FROM inserted
SELECT @vOld = intEntity from deleted
if(@vNew <> @vOld)
BEGIN
INSERT INTO tblAudit
(dtDateAdded, strUserName, strField, strOldData, strNewData, intRAC)
VALUES
({fn Now()}, @ModBy, 'intEntity',@vOld,@vNew, @ID)
END
Side note, i added the column intRAC to track which record was updated...
--
David Lozzi
Product Development Manager
Delphi Technology Solutions
www.delphi-ts.com - www.lozzi.net
"David Lozzi" <dlo...@delphi-ts.com> wrote in message
news:5C099FD8-69D8-48C6...@microsoft.com...
--CELKO--
You need to talk to an accountant and a security guy before you get in
trouble. You cannot keep the audit trail in the same DB as the data.
It is illegal as well as a bad idea.
Get a third party tool that will do this for you from log files. It
will help keep you out of jail and lawsuits.
Next, please take a course in basic RDBMS. We do not put the data
type in the data element name (this is SQL and not BASIC any more); we
do not use IDENTITY for a key (it is proprietary and non-relational,
while your time stamp and "field_name" are a natural key); a table
with all NULL-able columns is a disaster waiting to happen; TEXT is
proprietary; you apparently don't know that columns and fields are
totally different concepts from the names you gave one of your
columns; you know you are dealing with an ACCESS programmer when you
see the "magical, universal" VARCHAR(50) data type all over the
place.
Alex Kuznetsov
why don't you stop disseminating this nonsence. Under some
circustances, it is indeed illegal. Under many other circumstances, it
is perfectly legal. Nothing in the origianl post indicates one way or
another.
"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs [legal environment in Austin, TX] of his tribe and island
[Austin, TX] are the laws of nature [the entire IT community]" -
Caesar and Cleopatra; George Bernard Shaw 1898
SQL Menace
1) you did not code for multi row updates, join deleted with updated
tables instead
2) if(@vNew <> @vOld) If one of those is null you have a problem
3) fn now, I would use getdate() or current_timestamp
you will have to use a bunch of if update statements
if update(intEntity )
begin
--code here
end
if update(strLastModifiedBy )
begin
-- code here
end
etc etc etc
take a look here :http://sqlservercode.blogspot.com/2005/12/fun-with-
sql-server-update-triggers.html
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Dec 12, 10:07 am, "David Lozzi" <dlo...@delphi-ts.com> wrote:
> I've done the following, however I am going to have to do this for 40+
> fields. Is there another way?
>
> DECLARE @vOld as varchar(1000)
> DECLARE @vNew as varchar(1000)
> DECLARE @ModBy as varchar(100)
> DECLARE @ID as int
>
> SELECT @ModBy = strLastModifiedBy FROM inserted
> SELECT @ID = ID from inserted
>
> SELECT @vNew = intEntity FROM inserted
> SELECT @vOld = intEntity from deleted
> if(@vNew <> @vOld)
> BEGIN
> INSERT INTO tblAudit
> (dtDateAdded, strUserName, strField, strOldData, strNewData, intRAC)
> VALUES
> ({fn Now()}, @ModBy, 'intEntity',@vOld,@vNew, @ID)
> END
>
> Side note, i added the column intRAC to track which record was updated...
>
> --
> David Lozzi
> Product Development Manager
> Delphi Technology Solutionswww.delphi-ts.com-www.lozzi.net"David Lozzi" <dlo...@delphi-ts.com> wrote in message
>
> news:5C099FD8-69D8-48C6...@microsoft.com...
>
>
>
> > Howdy,
>
> > Here's the deal. I know I can create a trigger to insert all of the fields
> > into another table that is the same structure as the original table. What
> > I want to do is loop through all of the fields (40ish) and track only the
> > fields that have changed then store that information in a table, as seen
> > below
>
> > CREATE TABLE [dbo].[tblAudit](
> > [ID] [int] IDENTITY(1,1) NOT NULL,
> > [dtDateAdded] [datetime] NOT NULL CONSTRAINT [DF_tblAudit_dtDateAdded]
> > DEFAULT (getdate()),
> > [strUserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [strField] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [strOldData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [strNewData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>
> > So, is there a way to loop through the columns of a table in TSQL and then
> > compare them. I can think it through in VB easy enough, don't know abou
> > TSQL.
>
> > Thanks!
>
> > --
> > David Lozzi
> > Product Development Manager
> > Delphi Technology Solutions
> >www.delphi-ts.com-www.lozzi.net- Hide quoted text -
>
> - Show quoted text -
--CELKO--
So of course, you want to err in the most dangerous direction? And
those "other circumstances" are becoming fewer and fewer.
Alex Kuznetsov
> >> why don't you stop disseminating this nonsense. Under some circumstances, it is indeed illegal. Under many other circumstances, it is perfectly legal. Nothing in the original post indicates one way or another. <<
>
> So of course, you want to err in the most dangerous direction?
There is an easy way not to err at all - do not jump to conclusions
without knowing the fact.
> And
> those "other circumstances" are becoming fewer and fewer.
Do you have any statistics to back up this claim?
Geoff Schaller
Just because you saw the letters a*u*d*i*t in a table name doesn't imply
he or anyone suddenly needs your legal opinion (which was wrong anyhow).
Can't you just answer the question without the pompous pontificating on
top?
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:7242c1b4-23fd-4a67...@d21g2000prf.googlegroups.com:
--CELKO--
Read any business magazine and Google up SOX and other recent legal
developments. Look at the number of companies that have canceled IPOs
because of SOX and other regulations. Here in Austin Freescale
Semiconductor was one and they are not a small company. Research the
number companies that are buying back stock, so they can go private,
such as DELL.
Tony Rogerson
> developments. Look at the number of companies that have canceled IPOs
> because of SOX and other regulations. Here in Austin Freescale
> Semiconductor was one and they are not a small company. Research the
> number companies that are buying back stock, so they can go private,
> such as DELL.
What absolute TOSH.
> developments. Look at the number of companies that have canceled IPOs
> because of SOX and other regulations. Here in Austin Freescale
Post just ONE example of this (URL please), all those magazines have
electronic copy so it shouldn't be difficult.
> Semiconductor was one and they are not a small company. Research the
> number companies that are buying back stock, so they can go private,
> such as DELL.
"Companies" do not buy back stock; "People" be they a number of venture
capitalists or major share holders buy stock in order to take control of the
company in the hope they can take the company back private so they can make
more profit.
This has NOTHING at all to do with SOX.
Again, post just ONE article (URL) with this research otherwise shut up with
your lame attempts to generate business through myth spreading.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
SQL Menace
Companies do buy back stock. remember the buy back MSFT did a while
back(see link below)? This is done so that earning per share appears
higher since there are less outstanding shares
http://www.microsoft.com/presspass/press/2004/jul04/07-20boardPR.mspx
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
> Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson
Alex Kuznetsov
Anecdotal evidence is no substitute for solid statistics.
Alex Kuznetsov
I recently talked to someone who knows more about SOX than I do. He
says you just don't get it.
He says that although SOX does require a separate audit trail which
cannot be tampered with, it (SOX) does not prohibit to have other ways
of recording changes, including in the same database. So it looks like
you are absolutely wrong claiming that it is illegal.
--CELKO--
Would the Sarbanes-Oxley Compliance Journal do? One of the Freescale
articles appears here:
http://www.s-ox.com/news/detail.cfm?articleID=1957
It gives you an idea what the extra overhead was like.
>> "Companies" do not buy back stock; "People" be they a number of venture capitalists or major share holders buy stock in order to take control of the company in the hope they can take the company back private so they can make more profit. <<
In the U.S. Corporations are "lawful persons" and can buy back their
own stock. This allows the company to go private and escape many of
the new reporting requirements that apply to public companies. That
is where the profit is made. In the case of Freescale, they did an
IPO in 2004 and went private in 2006 November.
--CELKO--
I worked for someone who knows more about SOX than I do; I reviewed
the schema for their SOX compliance software product.
>> He says that although SOX does require a separate audit trail which cannot be tampered with, it (SOX) does not prohibit to have other ways of recording changes, including in the same database. <<
The law does not specify method, only results. Now, explain how
having audit columns in the table being tracked separates the audit
trail from the data. How would you require that two or more
"signatures" appear on each change to that table? You cannot do it
with this schema and that is what makes it illegal.
What you want to have is an automated system that see if "things
balance" -- if we made a shipment, there was a purchase order for it
(nobody is mailing stuff to themselves); if we write a check, there is
an authorization with a proper account for it (no ENRON-style
retirement programs); etc.
Putting the audit trail in the same table is like having the log file
on the same hard drive. You lose everything when it crashes.
Alex Kuznetsov
> >> I recently talked to someone who knows more about SOX than I do. <<
>
> I worked for someone who knows more about SOX than I do; I reviewed
> the schema for their SOX compliance software product.
>
> >> He says that although SOX does require a separate audit trail which cannot be tampered with, it (SOX) does not prohibit to have other ways of recording changes, including in the same database. <<
>
> The law does not specify method, only results. Now, explain how
> having audit columns in the table being tracked separates the audit
> trail from the data.
Let us get to basic Logic 101.
1. What makes you think David's system is subject to SOX or similar
regulations?
2. What makes you think David's tblAudit table is used to comply to
SOX?
Nothing in the post indicates neither. If you make conclusions based
on assumptions instead of facts you are likely tog et it wrong.
Garbage in, garbage out.
> How would you require that two or more
> "signatures" appear on each change to that table? You cannot do it
> with this schema and that is what makes it illegal.
No, it is only illiegal if it is used to comply with SOX. You don't
know if that is the case.
Tony Rogerson
> Look at the number of companies that have canceled IPOs
> because of SOX and other regulations. Here in Austin Freescale
> Semiconductor was one and they are not a small company
From the article "In December 2005, Freescale became completely independent
following the distribution of Motorola?s remaining shares to existing
Motorola shareholders. "
Have you even read that article?
Here: still traded: http://finance.yahoo.com/q?s=FSL-B not private.
They never cancelled SOX, where on this planet does it say that? Absolute
rubbish.
STOP MYTH SPREADING.
--CELKO--
SOX et al are the reason that companies are looking at what they are
doing.
But logic dictates that the audit trail has to be outside of what it
audits. Even if he is not subject to legal requirements, he is
subject to logic and standard practices.
Would you please demonstrate to me how he will recover his audit data
from a row that is deleted if that audit data is also in that row?
Tony Rogerson
Pottle, kettle black :- you are nothing but a cowboy 'period'.
Tony Rogerson
> back(see link below)? This is done so that earning per share appears
> higher since there are less outstanding shares
> http://www.microsoft.com/presspass/press/2004/jul04/07-20boardPR.mspx
>
Rules are different here in the UK I guess.
Alex Kuznetsov
> >> 1. What makes you think David's system is subject to SOX or similar regulations? <<
>
> SOX et al are the reason that companies are looking at what they are
> doing.
>
Even though you do not get it yet, clearly there are many other
reasons beyond legal ones to record who changed what and when. If you
were willing to learn, I could give you many examples.
>
> Would you please demonstrate to me how he will recover his audit data
> from a row that is deleted if that audit data is also in that row?
That is unnecessary because the OP did not request need to recover
audit data.
Tony Rogerson
> doing.
They are NOT buying back or going private because of SOX.
Post a URL that clear states that or frankly even infers that.
And as to Freescale Seminconductor which you state bought back it's shares
because of SOX, well, this is reality - somewhat different from what you
state...
http://fsl-b.bloggingstocks.com/
Freescale Semiconductor still trying to recover
Freescale Semiconductor was bought by a private equity group in December
2006.....
results are still way behind comparable quarters before the company was
taken private
The company also took on a heavy debt load in its acquisition.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:4e0700d0-c9e7-4005...@t1g2000pra.googlegroups.com...
David Lozzi
I am just keeping track of changes to a table for the sake of being able to
yell at someone who messes up the data. That's all. So.... any ideas on how
to do it?
Thanks,
--
David Lozzi
Product Development Manager
Delphi Technology Solutions
www.delphi-ts.com - www.lozzi.net
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:165E02E2-8077-4BA3...@microsoft.com...
Tony Rogerson
I tend to create a seperate table with the same structure as the one you are
change tracking.
audit_<tablename>
I then add other columns like current_timestamp for the change, what sort of
op it was, who did it and what the program was.
I then use a UPDATE,DELETE trigger to insert into that table.
Here's a basic example....
create table basetable (
basetable_id int not null identity constraint pk_basetable_id
primary key clustered,
iso_country_code varchar(4) not null constraint
uk_basetable_iso_country_code unique
)
create table audit_basetable (
basetable_id int not null,
iso_country_code varchar(4) not null,
id int not null identity primary key clustered,
op_date datetime not null default( current_timestamp ),
op_who sysname not null,
op_what varchar(500)
)
go
create trigger trg_audit_basetable on basetable for update, delete
as
begin
insert audit_basetable (
basetable_id,
iso_country_code,
op_who,
op_what )
select basetable_id,
iso_country_code,
op_who = ( select loginame from master..sysprocesses where
spid = @@spid ),
op_what = ( select program_name from master..sysprocesses
where spid = @@spid )
from inserted
end
go
insert basetable ( iso_country_code ) values( 'abcd' )
insert basetable ( iso_country_code ) values( 'efgh' )
select * from basetable
select * from audit_basetable
update basetable set iso_country_code = upper( iso_country_code )
select * from basetable
select * from audit_basetable
Tony Rogerson
> method and a whole heck of a lot easier but my customer wants to just see
> the fields that changed and it's associated info, like.
>
> 12/14/07 field2 "three four"
> field6 12/5/07
> field10 "just cause"
>
> 12/12/07 Field1 5
> Field2 "one two"
> Field6 12/1/07
>
> But now that I think about it, if i used the same table structure, i could
> use a detailsview, then loop through the columns and check for data in
> each column then hide each empty row. What do you think of that?
If you are using SQL Server 2005, just work out what's changed in the
trigger and hold it as XML in an XML data type using the audit_basetable
table.
David Lozzi
think i'll do my previous idea.
Thanks for the help!
--
David Lozzi
Product Development Manager
Delphi Technology Solutions
www.delphi-ts.com - www.lozzi.net
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:77E07E38-115A-4D25...@microsoft.com...