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

Show quoteHide quote

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







Show quoteHide quote

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.

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


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.

rmg66 wrote:
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.

eagle wrote:
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!

Determining financial year quarter
==again== constraints with nulls
(Non) Updatable view problem
Help with this trigger
unique constraints with nulls