SQL Server - TimeStamp AutoUpdate

Trigger for Insert and Update

CREATE TABLE TimeStamp_AutoUpdate

(

[ID] INT IDENTITY(1,1), 

[Employee_ID] NVARCHAR(50) NUll,

[FirstName] VARCHAR(200) NULL,

[LastName] VARCHAR(200) NULL,

[FatherName] VARCHAR(200) NULL,

[DOB] NVARCHAR(10) NULL,

[Address] VARCHAR(200) NULL,

[Mobile] bigint Null,

[CreateDate] datetime not NULL,

[LastChanged] datetime not NULL

)

CREATE TRIGGER dbo.trgAfterInsertUpdate ON dbo.TimeStamp_AutoUpdate

AFTER INSERT, UPDATE 

AS

  UPDATE t 

  SET LastChanged = GETDATE()

  from dbo.TimeStamp_AutoUpdate t join inserted i

  on t.ID=i.ID

insert into TimeStamp_AutoUpdate values('amit_1201','Amit','Vaid','Sh.Vijay Vaid','26-04-1981','Vinod Puri','9711004147',getdate(),getdate())

update dbo.TimeStamp_AutoUpdate set firstname='AMIT' where id=1

We can also capture the username which is actually inserting/updating the record...

Create TRIGGER dbo.trgAfterInsertUpdate_HOSP_Patient_Registration_Table ON dbo.HOSP_Patient_Registration_Table

AFTER INSERT, UPDATE 

AS

  UPDATE t 

  SET LastChanged = GETDATE(), record_last_updated_by=SUSER_NAME()

  from dbo.HOSP_Patient_Registration_Table t join inserted i

  on t.ID=i.ID

Additionally

- 5 Mostly used DataTypes

a) int

b) char

c) varchar

d) nvarchar

e) datetime

- 4 Mostly used Constraints

a) Primary Key

b) Default

c) Foreign Key

d) Check

Best Regards

Amit Vaid

Popular Post

https://amit-sqldbasupport.blogspot.com/2022/12/sql-server-dba-sankalp.html