Why my after update trigger is executing on insert although I have separate trigger for insert as well

/* trigger for update */
create trigger add_date
on students 
after update
as
    update students 
    set dtModify = getdate()
    where id = (select id from inserted)

/* trigger for insert */
create trigger add_date_insert 
on students 
after insert
as 
    update students 
    set dtEnter = getdate() 
    where id = (select id from inserted)

Answer

First of all – for the INSERT – I would use a DATETIME2(3) column with a default constraint – then you do not need a trigger:

CREATE TABLE dbo.Students
(
    // your columns here
    dtEnter DATETIME2(3)
        CONSTRAINT DF_Students_dtEnter DEFAULT (SYSDATETIME())
)

Now, each time you insert a row and you do not specify the dtEnter column in your list of columns to insert values into – it will automatically be set by SQL Server:

INSERT INTO dbo.Students (list-of-columns-WITHOUT-dtEnter)
VALUES (list-of-values)

For the update, you need to use a trigger – can’t use a default constraint for that. Use this trigger – make sure to handle the fact that Inserted might contain multiple rows!

CREATE TRIGGER add_date
ON dbo.Students 
AFTER UPDATE
AS
    UPDATE dbo.students 
    SET dtModify = SYSDATETIME()
    WHERE id IN (SELECT id FROM inserted)