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

Question

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

Sachin Singh · Answer

@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

Sachin Singh · Answer

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 ;

G Y · Answer

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