Reemplazo para IF UPDATE()

Compara la columna en las tablas inserted y deleted, en inserted tienes el
valor nuevo y en deleted el antigüo.

"Berta Gomez" escribió en el mensaje
news:

Saludos.

Tengo un problemilla para detectar en un trigger si un campo ha sido
modificado. No lo puedo hacer con IF UPDATE() porque desde la aplicacion
le
estoy mandando siempre al update el campo aun este no haya cambiado (ya lo
tengo asi y no deseo cambiarlo). Lo que necesito es una manera de saber
si
el campo ha cambiado realmente, es decir si su valor anterior es distinto
del actual.

bye

Tene en cuenta que si tu aplicacion modifica mas de un registro a la
vez, vas a tener que revisar cada uno de los registros que encuentres
tanto en la tabla INSERTED como en la DELETED

you can also test using the columns_updated()

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on. COLUMNS_UPDATED returns multiple
bytes if the table on which the trigger is created contains more than 8
columns, with the least significant byte being the leftmost. COLUMNS_UPDATED
will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

bitwise_operator

Is the bitwise operator to use in the comparison.

updated_bitmask

Is the integer bitmask of those columns actually updated or inserted. For
example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether
columns C2, C3, and C4 are all updated (with table t1 having an UPDATE
trigger), specify a value of 14. To check whether only column C2 is updated,
specify a value of 2.

comparison_operator

Is the comparison operator. Use the equal sign (=) to check whether all
columns specified in updated_bitmask are actually updated. Use the greater
than symbol (>) to check whether any or some of the columns specified in
updated_bitmask are updated.

column_bitmask

Is the integer bitmask of those columns to check whether they are updated or
inserted.

any adcional information plase contact me at or on the
news Brazil-Portuguese Sql server.

Regards.

"Berta Gomez" wrote:

Saludos.

Tengo un problemilla para detectar en un trigger si un campo ha sido
modificado. No lo puedo hacer con IF UPDATE() porque desde la aplicacion le
estoy mandando siempre al update el campo aun este no haya cambiado (ya lo
tengo asi y no deseo cambiarlo). Lo que necesito es una manera de saber si
el campo ha cambiado realmente, es decir si su valor anterior es distinto
del actual.

bye

Pero Columns_updated() compara los valores de inserted y deleted ? es decir
solo toma como actualizados los que realmente han cambiado ?


"MVP Marcelo Colla" <MVP Marcelo wrote in
message news:

you can also test using the columns_updated()

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column

or

columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or

updated.

The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on. COLUMNS_UPDATED returns multiple
bytes if the table on which the trigger is created contains more than 8
columns, with the least significant byte being the leftmost.

COLUMNS_UPDATED

will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

bitwise_operator

Is the bitwise operator to use in the comparison.

updated_bitmask

Is the integer bitmask of those columns actually updated or inserted. For
example, table t1 contains columns C1, C2, C3, C4, and C5. To check

whether

columns C2, C3, and C4 are all updated (with table t1 having an UPDATE
trigger), specify a value of 14. To check whether only column C2 is

updated,

specify a value of 2.

comparison_operator

Is the comparison operator. Use the equal sign (=) to check whether all
columns specified in updated_bitmask are actually updated. Use the greater
than symbol (>) to check whether any or some of the columns specified in
updated_bitmask are updated.

column_bitmask

Is the integer bitmask of those columns to check whether they are updated

or

inserted.

any adcional information plase contact me at or on the
news Brazil-Portuguese Sql server.

Regards.

"Berta Gomez" wrote:

> Saludos.
>
> Tengo un problemilla para detectar en un trigger si un campo ha sido
> modificado. No lo puedo hacer con IF UPDATE() porque desde la

aplicacion le

> estoy mandando siempre al update el campo aun este no haya cambiado (ya

lo

> tengo asi y no deseo cambiarlo). Lo que necesito es una manera de saber

si

> el campo ha cambiado realmente, es decir si su valor anterior es

distinto

> del actual.
>
> bye
>
>
>