SQL Syntax: Table trigger

If a user releases a sales order from suspense the sequence will be: -

1. OrderStatus ="S"

2. User logs in to order, OrderStatus ="0"

3. User releases order, OrderStatus ="1" or "4" or "8"

I can't see how I can track the change from "S" to "1" or "4" or "8"

That is not true, the UPDATE() will only be true if the Column is in the UPDATE SET statement.

So if I did
UPDATE dbo.Table
SET mycolumn = 'U'

Then only UPDATE(mycolumn) will return true UPDATE(OrderStatus) will not return true.  it is not about rows, it is about columns.  If you check the inserted and deleted tables for OrderStatus column, the values would be the same.

@Ben, read it again:

UPDATE dbo.Table SET mycolumn = mycolumn

willl touch every row and set the value of mycolumn to its old value. So there is no change in the value. It's only touched. This is semantically important when you apply business rules á la when this changed do that.

For a concise example see here or:

USE tempdb;
GO

CREATE TABLE UpdateTest
    (
      ID INT ,
      Payload NVARCHAR(8) ,
      UpdateDetectedByColumnsUpdated BIT ,
      UpdateDetectedByUpdate BIT ,
      UpdateDetectedByComparision BIT,
    );
GO

CREATE TRIGGER tr_UpdateTest ON UpdateTest
    AFTER UPDATE
AS
    SET NOCOUNT ON;              

    IF ( COLUMNS_UPDATED() & 2 ) = 2
        UPDATE  T
        SET     UpdateDetectedByColumnsUpdated = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;
             
    IF UPDATE(Payload)
        UPDATE  T
        SET     UpdateDetectedByUpdate = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;            

    UPDATE  T
    SET     UpdateDetectedByComparision = 1
    FROM    UpdateTest T
            INNER JOIN Inserted I ON I.ID = T.ID
            INNER JOIN Deleted D ON I.ID = D.ID
                                    AND I.Payload <> D.Payload;
GO

INSERT  INTO UpdateTest
VALUES  ( 1, '1', 0, 0, 0 ),
        ( 2, '2', 0, 0, 0 ),
        ( 3, '3', 0, 0, 0 ),
        ( 4, '4', 0, 0, 0 );

-- Single row update, change of the value.
UPDATE  UpdateTest
SET     Payload = '11'
WHERE   ID = 1;

-- Single row update, no change of the value.
UPDATE  UpdateTest
SET     Payload = '2'
WHERE   ID = 2;

-- Display the results.
SELECT  *
FROM    UpdateTest;

-- Reset the indicators.
UPDATE  UpdateTest
SET     UpdateDetectedByColumnsUpdated = 0 ,
        UpdateDetectedByUpdate = 0 ,
        UpdateDetectedByComparision = 0;

-- Multi-row update, changing only row 1 and 3.
UPDATE  UpdateTest
SET     Payload = '2'
WHERE   ID <= 3;

-- Display the results.
SELECT  *
FROM    UpdateTest;

DROP TABLE UpdateTest;
GO

Select allOpen in new window

I understand what you mean, but that is still why I check in my trigger example to see if the value is still 'S' because I could set the value of OrderStatus to 'S' when it is already S, so if you check to see if it is still S then you have validated that it has not changed to another status.

The requirements from the OP is that when the OrderStatus changes from S it is to be tracked and email sent.

Am I missing something?

The second part, Ben:

That a trigger is executed per statement. Thus in DELETED and INSERTED may be more than one row. Change the trigger in the above sample:

CREATE TRIGGER tr_UpdateTest ON UpdateTest
    AFTER UPDATE
AS
    SET NOCOUNT ON;              

    IF ( COLUMNS_UPDATED() & 2 ) = 2
        UPDATE  T
        SET     UpdateDetectedByColumnsUpdated = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;
             
    IF UPDATE(Payload)
        UPDATE  T
        SET     UpdateDetectedByUpdate = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;            

    UPDATE  T
    SET     UpdateDetectedByComparision = 1
    FROM    UpdateTest T
            INNER JOIN Inserted I ON I.ID = T.ID
            INNER JOIN Deleted D ON I.ID = D.ID
                                    AND I.Payload <> D.Payload;

    DECLARE @oldOrderStatus varchar(5), @newOrderStatus varchar(5), @rows_affected INT;
	SELECT @rows_affected = COUNT(*) FROM DELETED;
    SELECT @oldOrderStatus = Payload FROM DELETED;
    SELECT @newOrderStatus = Payload FROM INSERTED;

	DECLARE @Message NVARCHAR(255);
	SET @Message = 'Rows touched: ' + CAST(@rows_affected AS NVARCHAR(255)) + ', Old Value: ' + @oldOrderStatus + ', New Value: ' + @newOrderStatus + '.';
	PRINT @Message;
GO

Select allOpen in new window

Your trigger ignores that and will work with some arbitrary values from the virtual tables DELETED and INSERTED.

Mine was only an example, but you are right it is very possible that the deleted and inserted tables contain more than 1 row.

Yup, especially as most updates are one row updates. And then one day some does a batch update and now try to pin down that error...

My problem is the program front end sets the 'OrderStatus' to zero when an order is maintained so the trigger fires. I only want the trigger to fire on a genuine status change. Is this possible?
Quite unrelated, but using a TRIGGER to send an email is not a good idea.  Triggers are meant to be lightweight and adding functionality to send an email makes it non-scalable.

Thanks everyone for trying to help