SQL Server trigger not firing properly

I have a trigger which I thought would only update when one of the columns was updated not the table. Is there a way to rewrite this so it only fires when the specified columns are updated not开发者_StackOverflow社区 other fields within the table

CREATE TRIGGER [afm].[afm_rm_dwgs_t] ON [afm].[rm] 
   FOR UPDATE
AS
   IF (UPDATE(area) OR UPDATE(dv_id) 
   OR UPDATE(dp_id) OR UPDATE(rm_cat) 
   OR UPDATE(rm_type) OR UPDATE(rm_std))
   BEGIN
      SET NOCOUNT ON;
      UPDATE afm.afm_dwgs 
      SET dwg_updt = 1 
      WHERE afm_dwgs.dwg_name IN (SELECT dwgname FROM inserted)
   END

suppose that you want to check if the area column is updated, then the code will look like this:

declare @oldArea varchar(50)
declare @newArea varchar(50)

select @oldArea= area from deleted
select @newArea=area from inserted

if ( @oldArea <> @newArea)
-- area is updated

No, the trigger is defined as FOR UPDATE ON [afm].[rm], so it will always fire for each update on that table. There's no way to restrict that upfront.

Your check inside the trigger will then eliminate any "superfluous" trigger activations from actually doing anything.

UPDATE() only means that the column was included in the data set. It makes no representation as to whether the underlying data is different.

You'll have to get a little more complicated than this.

You need to compare the DELETED and INSERTED values in order to see if there is a change.

Check this link for a little more information.