How to create a time log / event tracking timestamped based?

I have an existing table populated with data.
I have a frontend vb that is to maintain the database.
I'd like to record any adds, updates or deleted records?

How is that done by design?
zachvaldezAsked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
You could always do it with one trigger :  

create  trigger ChangeLog  on table1
for insert, update, delete
as
begin
insert into tabalog select 'insert', getdate(), cola, colb.... from inserted
insert into tabalog select 'delete', getdate(), cola, colb.... from deleted
end
0
 
FDzjubaCommented:
add to table you want to track


CREATE TRIGGER ChangeLog
ON table1
FOR INSERT, UPDATE , DELETE
AS
INSERT INTO ChangeLog VALUES (getdate())

you can add stuff like
IF UPDATE ( column )
BEGIN
END

and so on for full instruction you will need to look into TRIGGERS a bit deeper, or post follow up in here we will guide you
0
 
Eugene ZCommented:
or\and use 3-party tools like lumigent Entegra
http://www.lumigent.com/products/
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
zachvaldezAuthor Commented:
Thanks for the tips. Buying a 3rd party tool is a good idea but not an option right now.
I would like to know more about how triggers are used in consonant witht  FDzjuba's comments
with regards to time log/timestamp application..
0
 
FDzjubaConnect With a Mentor Commented:
Trigger is fired every time you do
 INSERT, UPDATE or  DELETE, you need to assigne trigger to each table you want to track changes in.
So, when user modifies table1 trigger  ChangeLog fires and inserts record into ChangeLog_table,

---
CREATE TRIGGER ChangeLog
ON table1
FOR INSERT, UPDATE , DELETE
AS
INSERT INTO ChangeLog_table VALUES (getdate())
---
that would be more correct way:) (trigger name and table name were the same in the first example )

you simply add record in ChangeLog_table stating what time update has been made on the table. Is that what you are chasing ?
0
 
zachvaldezAuthor Commented:
In triggrs, I would like to add the id and dttime to another table, say table 2.
If I add it in table 1, it would add also in table 2.. that's what I meant. HOw is that implemented.

Good examples -maybe

Thanks
0
 
KhunJeanConnect With a Mentor Commented:
CREATE TRIGGER ChangeLog
ON table1
FOR INSERT
AS
INSERT INTO ChangeLog_table
  select id,getdate  -- Here you can select whatever fields you want
  from inserted

CREATE TRIGGER ChangeLog
ON table1
FOR UPDATE, DELETE
AS
INSERT INTO ChangeLog_table
  select id,getdate  -- Here you can select whatever fields you want
  from deleted

id is taken from the deleted record. With an update the previous content will be in the deleted table and the new content in the inserted table.


Read this part of books online.

<snip>
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
</snip>

0
 
FDzjubaCommented:
for deleted records

CREATE TRIGGER ChangeLog ON table1
FOR DELETE
AS
insert into table2 select 'DELETE',getdate(),[all fields from table1]  from deleted
GO

CREATE TRIGGER ChangeLog ON table1
FOR INSERT
AS
insert into table2 select 'DELETE',getdate(),[all fields from table1]  from inserted
GO
0
 
FDzjubaCommented:
well yes, i just don't know which one he needs to do :)
0
 
zachvaldezAuthor Commented:
IN triggers, why are the names inserted,deleted, etc. but I don't see the names addressed where? Are these SQL default,acceptable names?
0
 
BillAn1Commented:
yes, INSERTED and DELETED are 2 special tables which are automatically created and populated. They contain the row(s) inserted / deleted that caused the trigger to fire. In the case of an update statement, the old values of the row are put in hte DELETED table, and the new values in the INSERTED table.
0
 
zachvaldezAuthor Commented:
Thanks for all the excellent responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.