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...
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
No comments:
Post a Comment