CODEWITHSUNDEEP
sql-serverdatabase
jeffw
jeffw

Reputation: 21

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 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

Upvotes: 2

Views: 568

Answers (3)

marc_s
marc_s

Reputation: 754368

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.

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88044

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.

Upvotes: 0

Ali Tarhini
Ali Tarhini

Reputation: 5358

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

Upvotes: 1

Related Questions