Update trigger to be initiated, when there is any change on any column

G Y

5y857

Update trigger to be initiated, when there is any update on any column in dummytable other than ModifiedDate and UserModified column changes (update trigger should not consider the changes on ModifiedDate and UserModified columns)

CREATE TABLE [dbo].[dummytable](
[ID] [int] NOT NULL,
[code] [varchar](50) NULL,
[codevalue] [varchar](50) NULL,
[codedescription] [varchar](50) NULL,
[IsAdmin] [bit] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[createdUser] [int] NULL,
[CreatedDate] [datetime] NULL,
[UserModified] [int] NULL,
[ModifiedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
@Gopi this was just an example you can always do something like below
create trigger tr_Name
after update of column1, column2, ... -- other columns, except  SpececificColumn
on your_table
+3
G Y5y ago
Sachin Singh
Yes, we can use UPDATE() but if there are more that 30 columns in the table then applying OR operator does not seems to be a good practice
0
Sachin Singh5y ago
you can use the Update() function , for specific columns
CREATE TRIGGER trg_DummyTable
ON dummytable
AFTER  UPDATE
AS
IF ( UPDATE (code)OR UPDATE (codevalue)OR UPDATE (codedescription)OR UPDATE (IsAdmin)OR UPDATE (FirstName)OR UPDATE (LastName) )
BEGIN
UPDATE dummytable
SET FirstName= FirstName1 --- anything that you want
WHERE id IN (SELECT DISTINCT id FROM inserted)
END;
+1

Update trigger to be initiated, when there is any change on any column

Sachin Singh