Re: Trigger

On Thu, 21 Apr 2005 21:47:25 +0100, Al Newbie wrote:

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

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

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: Display Rows Into Coulumn
    ... "steve" wrote in message ... > I have the following rows that I would like to display into coulumn ... > SELECT StockCode, Warehouse, Qty ...
    (microsoft.public.sqlserver.programming)
  • Display Rows Into Coulumn
    ... SELECT StockCode, Warehouse, Qty ... FROM StkMaster ...
    (microsoft.public.sqlserver.programming)