>The subquery is failing because it is returning multiple rows.

SQL Server triggers fire once per DML, regardless of the number of rows that are affected by the DML - 1 row, multiple rows, or no rows. All triggers need to be created to handle multiple rows.

-Mike



>
>>What about this fails? Fails to compile? Fails to execute the way that you want it to?
>>
>>You can expect [(select cOrderType from deleted) <> 'X'] to fail if the subquery returns multiple rows.
>>
>>-Mike
>>
>>>Hello,
>>>
>>>We are using a trigger that updates a date field on two tables every time one of those tables is updated. We do not want the trigger to fire if the user_name is BackEnd, or if Order.cOrderType has been changed to or changed from an 'X'. The following is the code:
>>>
>>>CREATE TRIGGER OrderEdit ON dbo.Order
>>>FOR INSERT, UPDATE
>>>AS
>>>IF (SELECT user_name()) <> 'BackEnd' or (select cOrderType from deleted) <> 'X' or (select cOrderType from inserted) <> 'X'
>>> BEGIN
>>> UPDATE dbo.Order
>>> SET tEdited = GETDATE()
>>> WHERE iOrder IN
>>> (SELECT iOrder FROM inserted)
>>> UPDATE dbo.Customer
>>> SET tEdited = GETDATE()
>>> WHERE iCustomer IN
>>> (SELECT iCustomer FROM inserted)
>>> END
>>>
>>>I have also tried nesting the IF statements and failed.
>>>
>>>TIA
>>>
>>>-Justin