Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted
Your trigger needs to leverage the 'virtual inserted' table as in the following example:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT
AS
BEGIN
    UPDATE X 
    SET ModifiedDate = CURRENT_TIMESTAMP
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies 
                                     -- the tuples
END 


AS an alternative to a trigger, why not simply assign a default value to that column.  Example:

ALTER TABLE myTable 
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT CURRENT_TIMESTAMP FOR myColumn


example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

CREATE TABLE #temp (
    col1 VARCHAR(10)
    ,col2 DATETIME
    )

ALTER TABLE #temp ADD CONSTRAINT ConstraintForTemp DEFAULT CURRENT_TIMESTAMP
FOR col2

INSERT INTO #temp (col1)
VALUES ('abc')

SELECT *
FROM #temp