How to track when a row was created/updated automatically in SQL Server?

You can have a RowCreated DATETIME DEFAULT (GETDATE()) to handle the date when the row was initially created automatically - but there's nothing short of writing a trigger to do the same for a RowModified DATETIME column.

For each table, you'd have to write an AFTER UPDATE trigger to handle the RowModified column something like this:

CREATE TRIGGER dbo.trg_YourTableUpdated
ON dbo.YourTable AFTER UPDATE
AS BEGIN
    UPDATE dbo.YourTable
    SET RowModified = GETDATE()
    FROM INSERTED i
    WHERE dbo.YourTable.ID = i.ID
END

Have a look at AutoAudit

This will add columns to a table and create DML triggers to manage those columns as well as maintain the audit trail table

I've found it to be very useful

It's certainly possible to use DDL triggers to attempt to enforce table design issues. It's also possible to automate checking the metadata for design smells

You will have to implement this with DML triggers in SQL Server, for the insert you could get away with defaults but if someone passes in a value it will overwrite the default