We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to automatically update the last modified date in SQL 2000 tables?

ashugarg00
ashugarg00 asked
on
Medium Priority
823 Views
Last Modified: 2008-01-09
Hi,

I have a large database with about 70 tables. I would like to maintain a "Last Modified Date" column on all the tables. What is the best way to automatically update the last modified date whenever a record in a table is updated?

Need this urgently!!!!

Thanks,
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
ashugarg00,
> What is the best way to automatically update the last modified date
> whenever a record in a table is updated?


Yoou can create a trigger for INSERT AND UPDATE to do this
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:



CREATE TRIGGER trUpdateDate
ON urTable
FOR INSERT,UPDATE
AS
UPDATE A
SET defDate = GETDATE()
FROM Inserted i
INNER JOIN urTable a ON a.PrimaryKey= i.PrimaryKey
GO

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Stephen LappinSenior Technologist
Commented:
If you are on an Oracle database, your trigger would look something like this:


CREATE OR REPLACE TRIGGER <trigger_name>
AFTER INSERT OR UPDATE ON <table_name>
FOR EACH ROW
BEGIN
  -- last modified date
 :New.Last_Modified_Date := SYSDATE;
  -- who performed mod
 :New.Last_Modified_User := USER;
END;
Hi ashugarg00,

If the updation or insertion is made to urTables from the code then I think it's very easy to apply getdate() to that column is quite easy. Otherwise you need to create the triggers for all the table that is not good. Actually if you want to update a table when there is a insertion or updation to that table is made then trigger is good but instead you can do it by passing directly the current datetime to that field (Last updated Field). Can u describe ur problem in detail  please?

Regards,
Mukesh
CERTIFIED EXPERT
Commented:
Just add a column to your table with a name like

last_update of datetime type

and update it along with other fields every time you do an update using

last_update = gatdate() (in MS SQL).

You could also create a "create_date" to store the insert moment of a row. This field should be set as having as default value the same getdate() function, which returns the curent date-time with milisecs.
Commented:
Add an ON UPDATE TRIGGER, and update the column with the getdate().

Best Regards,

Paulo Condeça.
If you are talking about putting a datestamp on each table, just add a datetime column (date_modified) if you don't already have it, and then create a trigger or two on each table.

alter table [table name]
add [date_modified] datetime not null
go

create trigger [Trigger name]
on [affected_table]
for  update AS
update t1
set [last_moodified] = getdate()
[affected table] t1 inner join updated u on t1.key = u.key
go

create trigger [Trigger name]
on [affected_table]
for  update AS
update t1
set [last_moodified] = getdate()
[affected table] t1 inner join inserted i on t1.key = i.key
go

Is this what you are asking for??? or are you talking about a table that is to contain all the table names with the last modified date??

Commented:
If it is for logging pursoses,

The best practice is to create an After Update Trigger, and then insert or update a record on other table.

Best Regards,

Paulo Condeça.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.