I want to be able to check if column 27 has been updated either on an insert
or an update.
If this column has been updated with a value I want to insert the following
fields into a different table (InvMov) within the database:

StockCode, Warehouse, TrnYear, TrnMonth, EntryDate, TrnQty and Movement Type

How would I go about achieving this? Is it something to do with
COLUMNS_UPDATED? I'm not sure what BOL is telling me.

Thanks

[quoted text, click to view]

Hi Al,

Why specifically column 27? What is someday, someone rebuilds your table
and has the columns listed in a different order - will you still need to
test for column 27 in the trigger, or do you actually want to test for
the same column?

Officially, the columns in a table have no order. The only "official"
way to refer to a column is by name. I suggest you to test for update of
the column by name, not by position. You can do it with UPDATE(Column),
but be aware that the column is considered updated if it is mentioned in
the SET clause of the UPDATE statement, regardless of whether the value
actually changed or not.

If that is what you want, then try:

CREATE TRIGGER MyTrigger
ON MyTable AFTER INSERT, UPDATE
AS
IF UPDATE (MyColumnOfInterest)
INSERT INTO InvMov (StockCode, Warehouse, TrnYear, TrnMonth,
EntryDate, TrnQty, MovementType)
SELECT StockCode, Warehouse, TrnYear, TrnMonth,
EntryDate, TrnQty, MovementType
FROM inserted
go

Best, Hugo
--