sql server programming: 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

[quoted text, click to view]

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/microsoft.public.sqlserver/browse_frm/thread/=
362be1ca6f432e3e


HTH, jens Suessmeyer.

[quoted text, click to view]

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 seek out and blame the user(s) for the
designer's mistake!

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

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
--

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







[quoted text, click to view]

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


[quoted text, click to view]
That´s 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´s even worse !

create trigger XXX on table XXX (...)

See detailed explanations here...

http://groups.google.de/group/microsoft.public.sqlserver/browse_frm/thread/362be1ca6f432e3e


HTH, jens Suessmeyer.