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

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,
ashugarg00Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:



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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Stephen LappinConnect With a Mentor Senior TechnologistCommented:
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;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
prajapati84Connect With a Mentor Commented:
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
0
 
ZberteocConnect With a Mentor 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.
0
 
MrNeticConnect With a Mentor Commented:
Add an ON UPDATE TRIGGER, and update the column with the getdate().

Best Regards,

Paulo Condeça.
0
 
stdcitunitConnect With a Mentor Commented:
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??
0
 
MrNeticCommented:
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.
0
All Courses

From novice to tech pro — start learning today.