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
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