Solved

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

Posted on 2004-09-16
12
445 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:zachvaldez
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12075755
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12075903
or\and use 3-party tools like lumigent Entegra
http://www.lumigent.com/products/
0
 

Author Comment

by:zachvaldez
ID: 12076010
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
 
LVL 7

Assisted Solution

by:FDzjuba
FDzjuba earned 150 total points
ID: 12076092
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
 

Author Comment

by:zachvaldez
ID: 12077052
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
 
LVL 2

Assisted Solution

by:KhunJean
KhunJean earned 150 total points
ID: 12077570
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:FDzjuba
ID: 12077664
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 200 total points
ID: 12079747
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12079835
well yes, i just don't know which one he needs to do :)
0
 

Author Comment

by:zachvaldez
ID: 12084163
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12085889
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
 

Author Comment

by:zachvaldez
ID: 12095581
Thanks for all the excellent responses.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now