Update Date/Time field 2

[tis9700]

Hi Tek-Tips,
I'm a little confused. I'm new to SQL and usually design small Access database.
This time I want to create a Client/Server database with SQL Server 2000 as a backend and MS Acess Data Project as the frontend.
I created a table called MasterInstructor.
Sample of fields:
InstructorID (PrimaryKey), Name, Address, City, SSN, CertDate, NewRecordDate, UpdateRecordDate.

The field "NewRecordDate" is a date/time when the record was created.

The field "UpdateRecordDate" is a date/time of when the record was last updated.

I created a trigger (I hear they are evil):

CREATE TRIGGER [UpdateDate] ON [dbo].[MasterInstructor]
FOR UPDATE
AS
UPDATE MasterInstructor SET
UpdateRecordDate = GETDATE()

but it updated every record. I added WHERE InstructorID = @@IDENTITY and that didn't work.

What do I need to do with the WHERE clause in order to update that field whenever 1 or more records are updated from the client? I've seen some posts that mention psuedo insert table but I don't understand what that is?

I'm developing a healthy respect for anyone who is an expert at this point!

Thanks

 

[amrita418]

Try this update stmt in the trigger:

Update MasterInstructor 
Set UpdateRecordDate = GETDATE()
from MasterInstructor Inner Join Inserted
on (MasterInstructor.InstructorId = Inserted.InstructorId)

Regards,
AA

 

[amrita418]

I guess I did not look at your whole code, here is what you need:

1) You need to update on what table triggers this trigger?

2) Do you want to update this field whenever any field in the main table changes? If not you will need to add some fields to check for.

3) Should the update happen if value was set to itself i.e. not change?

Here is a sample code you can work with:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  TRIGGER trig_tableA_u
ON TableA
AFTER UPDATE
AS

IF UPDATE(Value) OR
   UPDATE(Name) OR
   UPDATE(Type)
 

BEGIN
  UPDATE TableA
     SET LastUpdated = GETDATE()
    FROM Inserted 
	 Inner Join Deleted
		On Inserted.ID = Deleted.ID
	 Inner Join TableA
		On TableA.ID = Inserted.ID
  WHERE Inserted.Value <> Deleted.Value
     OR Inserted.Name		<> Deleted.Name
     OR Inserted.Type	<> Deleted.Type

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Regards,
AA

 

[tis9700]

Thanks amrita418,
It worked perfect! So what is this inserted?

 

[tis9700]

Just saw your second post! I wanted to update "UpdateRecordDate" when any changes were made to a record so I could see when the last modification occured. Eventually, I want to include a field for the user who made the modification. Your first example of code worked perfect!
Thanks

 

[amrita418]

Inserted and Deleted are logical tables that store have the same structure as the original table and have information before and after the change.

Look into books online for more details.

Regards,
AA