/* 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)

CodePudding user response：

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)

CodePudding user response：

If you are getting confused by inserted then there is nothing to worry about. This table has the inserted row on which the trigger is struck.

Now there are only two such tables in SQL Server inserted and deleted

• In Case of Insert, inserted has the row inserted.
• In case of Delete, deleted has the row which is deleted.
• In case of Update, the row in question is first deleted and hence placed in deleted and then new row is inserted with updated data which you can find in table inserted. So you might think of update as a single operation when it's actually combination of two operations delete and insert.

If you want your trigger to run logic only in case of Update you can put a IF check like this:

If exists (Select * from inserted) and exists(Select * from deleted)

and place your trigger logic inside this block like this

If exists (Select * from inserted) and exists(Select * from deleted)
BEGIN
--Update Logic
END
If exists (Select * from inserted) and not exists(Select * from deleted)
BEGIN
--Inser Logic
END