[SOLVED] How do you update a datetime field in a table every time another field in the same table gets updated?

How do you update a datetime field in a table every time another field in the same table gets updated?

I have a table called YearTable with three columns (Year, Status, and LastUpdatedTime). Every time someone adds/updates a value into the year or status column LastUpdatedTime needs to be updated. I wrote a trigger for this, but now every time I try to add a new record I get an error message:

ErrorSource: .NetSqlClient Data Provider. Error Message: Cannot insert the value NULL into Column 'Year', table 'Budget.YearTable'; column does not allow nulls. Insert fails.

This is the trigger:

CREATE TRIGGER UpdateTrigger
ON  YearTable
AFTER INSERT,DELETE,UPDATE
AS 
BEGIN

insert into tblaudit(LastUpdatedTime)
values(GETDATE())

END
GO

Solution

You stated:

I wrote a trigger for this, but now every time I try to add a new record I get an error message:

ErrorSource: .NetSqlClient Data Provider. Error Message: Cannot insert the value NULL into Column 'Year', table 'Budget.YearTable'; column does not allow nulls. Insert fails.

From this, does this mean your updates and deletes are working? If that is the case, then it sounds like just like the error message states. When you're doing an insert into the table, you aren't supplying a value for the year column on the YearTable.

Have you tried disabling the trigger and seeing if the behavior exists when doing the same operations?

After further consideration of your question, I'm now assuming you're meaning that when a row in YearTable is updated, that that same row in YearTable has its LastUpdated column updated. Though now I'm not really sure where your tblAudit is coming from. Or why you would have the trigger created for deletes when there would be no row to update at that point.

I'm going to assume that your key on the table is year - if you don't currently have a key, you probably need one.

To handle the LastUpdated for inserts/updates you could use the following in your trigger (assuming year is your key):

UPDATE YearTable
SET LastUpdated = GetDate()
FROM inserted
where YearTable.Year = inserted.year