The accounting system that we use is not very normalized. There is not
primary key for the table (OrdDetail). The table has an id column
(A4glidentity) with an identity set, and that column is part of a unique
index. If it has to be a primary key, is there another workable method?
Another challenge is that the shipping process that deletes and inserts
the same row to change a value in an inconsequential column (status), does
not change the value in A4glidentity.
Will your method work here? If not, what else can I try?
"Steve Kass" <skassdrew.edu> wrote in message
> You need to refer to a primary key column, as in
> if exists (
> select * from inserted, deleted
> where inserted.primarykeycolumn = deleted.primarykeycolumn
> and inserted.req_ship_dt <> deleted.req_ship_dt
> Steve Kass
> Drew University
> Mark Simmerman wrote:
>> >I want to check a table to see if a column (req_ship_dt) has beenno> >with an update trigger using the following:
> >IF UPDATE (req_ship_dt)
> >I discovered that our accounting system will delete and insert a row withfollowing:> >change made to req_ship_dt, but change other columns during a shipping
> >process. This is enough to set off the trigger. I attempted theDELETED)> >
> >IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT req_ship_dt FROMDeleted> >AND UPDATE (req_ship_dt)
> >I hoped that by checking for a difference between the Inserted andsubquery> >tables for that column that I would eliminate the shipping process from
> >setting off the trigger. Here is the error message that I received:
> >Server: Msg 512, Level 16, State 1, Procedure ChangeOrdDateOEORDLINTrig,
> >Line 6
> >Subquery returned more than 1 value. This is not permitted when theexpression.> >follows =, !=, <, <= , >, >= or when the subquery is used as an>> >The statement has been terminated.
> >What is a valid way of checking for a change in a column under these
> >Thanks in advance.