Date of records

Is there a way to tell when a record has been added to a table? And by
whom? I have data in a database that is obviously new, as the records do
not exist in any of our backups, but the user has dated them with old dates.
I am sure someone is entering these records in erroneously, but I cannot
find out when it was done or who is doing it
Thanks for your help.There are several approaches to this
1) triggers writing to an audit table
2) using a column with the rowversion/timestamp datatype which will provide
you with a relative estimate of when the row was inserted modified relative
to other rows.
3) using a datetime column which has a default of getdate()
The problems with approaches 2 and 3 are that they will break applications
which do unqualified inserts selects
i.e.
insert into tableName1
select * from tableName2
If each column is name in the above insert select you will not have a
problem
the problem with approach 1 is an administrative burden and the triggers
will add latency to each DML operation
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eagle" <eagle@.yahoo.com> wrote in message
news:eJOxWeqAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Is there a way to tell when a record has been added to a table? And by
> whom? I have data in a database that is obviously new, as the records do
> not exist in any of our backups, but the user has dated them with old
> dates. I am sure someone is entering these records in erroneously, but I
> cannot find out when it was done or who is doing it
> Thanks for your help.
>|||Put a trigger on the table to either log the update or to send a notice to
you.
create trigger XXX on table XXX
for insert, update
as
insert into logtable values (getdate(), current_user)
or
create trigger XXX on table XXX
for insert, update
as
exec xp_sendmail
@.recipients = 'your_email_address'
@.subject = 'NEW UPDATE!"
@.message = 'Record added by ' + current_user + 'on ' + getdate()
See books on line for correct syntax and other examples.
Robert
"eagle" <eagle@.yahoo.com> wrote in message
news:eJOxWeqAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Is there a way to tell when a record has been added to a table? And by
> whom? I have data in a database that is obviously new, as the records do
> not exist in any of our backups, but the user has dated them with old
dates.
> I am sure someone is entering these records in erroneously, but I cannot
> find out when it was done or who is doing it
> Thanks for your help.
>|||That=B4s bad:
insert into logtable values (getdate(), current_user)
Insert statements wihtout column list are the hell to maintain. So its
preferable to always use named columns in the list.
insert into
logtable
(
SomeAuditColumn,
SomeOtherAuditColumn
)
values (
getdate(),
current_user
)
That=B4s even worse !
create trigger XXX on table XXX (...)
See detailed explanations here...
http://groups.google.de/group/micro...se_frm/thread/=
362be1ca6f432e3e
HTH, jens Suessmeyer.|||eagle wrote:
> Is there a way to tell when a record has been added to a table? And by
> whom? I have data in a database that is obviously new, as the records do
> not exist in any of our backups, but the user has dated them with old date
s.
> I am sure someone is entering these records in erroneously, but I cannot
> find out when it was done or who is doing it
> Thanks for your help.
If the business rule is that users can't enter old dates then why
doesn't the database enforce that rule through constraints or triggers?
Seems counter-productive to s out and blame the user(s) for the
designer's mistake!
David Portas
SQL Server MVP
--|||Ok Jens,
I think you need to lighten up.
I just gave a quick example to point the guy in the right direction.
I don't have time here to write a book on the subject of best practices.
Robert
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1134999166.811255.297170@.z14g2000cwz.googlegroups.com...
Thats bad:
insert into logtable values (getdate(), current_user)
Insert statements wihtout column list are the hell to maintain. So its
preferable to always use named columns in the list.
insert into
logtable
(
SomeAuditColumn,
SomeOtherAuditColumn
)
values (
getdate(),
current_user
)
Thats even worse !
create trigger XXX on table XXX (...)
See detailed explanations here...
http://groups.google.de/group/micro...r />
a6f432e3e
HTH, jens Suessmeyer.|||rmg66 wrote:
> Ok Jens,
> I think you need to lighten up.
> I just gave a quick example to point the guy in the right direction.
> I don't have time here to write a book on the subject of best practices.
> Robert
Hi Robert,
Corrections aren't intended to imply that your contributions are
unwelcome. Far from it - this is Usenet! I agree with Jens that your
triggers could do with some improvement. At least the first example
ought to be modified to reference the INSERTED table so as to preserve
for example the key columns or the date column that was modified.
In the second case I would advise against sending email from a trigger.
You can Google for my previous posts on this topic to understand why.
Finally, it's unfortunate that your third piece of advice isn't much
help either. The sample triggers given under the CREATE TRIGGER topic
in BOL are atrocious examples of worst practice.
David Portas
SQL Server MVP
--sql