quote:

---

Originally posted by Bustaz Kool

There are a couple of trigger specific functions that might be of use to you. They are UPDATE() and COLUMNS_UPDATED(). The first takes a column name returns true if the column actually changed value. The second returns a bit map of the columns which have changed. Some find this a bit hard to work with. You can find details on each in BOL.
If neither of these strike your fancy, you can join and inspect the values in the inserted and deleted pseudo tables. I wouldn't recommend it but you could.

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

---

quote:

---

Originally posted by ferrethouse

Is there an efficient way to code this? I have a "students" table that has a field called "lastloggedin". There is a trigger on this table that I want to fire if any field other than "lastloggedin" changes. There are 50 columns in this table so I'd prefer not to do the whole...

if inserted.lastloggedin <> lastloggedin and
inserted.col1 = col1 and inserted.col2 = col2 etc. etc. etc.

---

IF EXISTS( SELECT 1 FROM DELETED d
           INNER JOIN INSERTED i
           ON i.PK = d.PK
           AND i.Field1 <> d.Field1
           AND i.Field2 <> d.Field2
           AND i.Field3 <> d.Field3
           AND...
          )

your actual code

quote:

---

IF EXISTS( SELECT 1 FROM DELETED d
           INNER JOIN INSERTED i
           ON i.PK = d.PK
           AND i.Field1 <> d.Field1
           AND i.Field2 <> d.Field2
           AND i.Field3 <> d.Field3
           AND...
          )

---

           ON i.PK = d.PK
           AND 
           (
                  (i.Field1 <> d.Field1 COLLATE Latin1_General_BIN
                   OR (i.Field1 IS NULL AND d.Field1 IS NOT NULL)
                   OR (i.Field1 IS NOT NULL AND d.Field1 IS NULL))
               OR i.Field2 <> d.Field2 ...
          )

quote:

---

Originally posted by Kristen

quote:

---

IF EXISTS( SELECT 1 FROM DELETED d
           INNER JOIN INSERTED i
           ON i.PK = d.PK
           AND i.Field1 <> d.Field1
           AND i.Field2 <> d.Field2
           AND i.Field3 <> d.Field3
           AND...
          )

---

I don't think, in this sort of scenario, doing a test ahead of the actual code helps because there may be individual rows within the updated rows that satisfy the conditions, and other rows that don't, so I think you need to do it on the actual code (e.g. an INSERT or UPDATE), or if the code is more complex I would pre-select the PKs of the relevant rows into a @TableVar / #TempTable and JOIN that to other tables in the code.

Also, sadly, the test for "different" needs to be more complicated:

           ON i.PK = d.PK
           AND 
           (
                  (i.Field1 <> d.Field1 COLLATE Latin1_General_BIN
                   OR (i.Field1 IS NULL AND d.Field1 IS NOT NULL)
                   OR (i.Field1 IS NOT NULL AND d.Field1 IS NULL))
               OR i.Field2 <> d.Field2 ...
          )

leave off the COLLATE for any non-Char columns. You can leave the PK columns out of the tests (but including them, for completeness and future-proofing against maintenance-bugs will do no harm - they are, after all, going to match)

These WHERE clauses are a bit tedious to make so I generate them mechanically

---

quote:

---

Originally posted by Bustaz Kool

If I am correct im assuming that you want to avoid the condition where the only column that changed is a single specific "administrative" column then I'd use the COLUMNS_UPDATED() function. You'll need to figure out the correct mapping of your special column to the bit map but the test becomes does the bit map equal a specific value(s).

Visakh16, thanks for the elaboration on the UPDATE() function.

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

---

quote:

---

Originally posted by X002548

ummmm, what does your TRIGGER DO?

Do you only allow stored procedures to access or modify your data?

---

declare @bit int ,
			@field int ,
			@doCode bit,
			@char int
			select @field = 0
			set @doCode = 0
			while @field < (select max(colid) from syscolumns where id = (select id from sysobjects where name = 'students'))
			begin
				select @field = @field + 1
				select @bit = (@field - 1 )% 8 + 1
				select @bit = power(2,@bit - 1)
				select @char = ((@field - 1) / 8) + 1
				--select @char, @field, @bit
				if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
					BEGIN
						if (select name from syscolumns where colid = @field and id = (select id from sysobjects where name = 'students')) <> 'LastLoggedIn'
							set @doCode = 1
						else
							set @doCode = 0
					END
			END