More Trigger Trouble (Not Null)

[gaffonso]

I'm trying to implement a simple auditing feature by creating a trigger that writes a LastModifiedID and LastModifiedDate to columns on each table in my database.

The LastModifiedID and LastModifiedDate columns are not directly edited by a user, they rely on the triggers to be populated.

In a previous thread, Terry confirmed that Not NULL constraints were checked before triggers fired, meaning that "normal" inserts would fail to fulfill the LastModified Not NULL constraints since the trigger doesn't get a chance to fire before those constraints are checked.

I've got that fixed, but the problem gets weirder. The INSERT statement in my trigger looks like this:

ALTER TRIGGER LastModified_INSERT_UPDATE
ON dbo.VesselTypes
INSTEAD OF INSERT, UPDATE
AS

IF @@ROWCOUNT=0 RETURN

BEGIN
INSERT INTO VesselTypes
(LastModifiedID, LastModifiedDate)
VALUES
(1, GETDATE())
END

The problem with this is that the trigger's insert statement fails because other "normal" fields in the table have Not NULL constraints set and the trigger's INSERT is not fulfilling those constraints.

So how in the world do you insert into only the LastModified columns in a trigger when doing so fails to fulfill the other "normal" constraints for the table.

Do I need to dynamically alter the INSERTED table or something?

Thanks (and sorry to be a pest, this one is bending my brain),

- Gary


Some of the other columns in the various tables