SQL Syntax: Table trigger

I have some SQL that works partially, I am looking for a change in a field (OrderStatus) and sending an email when the status changes from "S" to anything else AND the OrderType ='O'. 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?

    select @nvchOldStatus = OrderStatus from deleted
    select @nvchNewStatus = OrderStatus from inserted
	select @nvchDocType = DocumentType from inserted
    -- If there has been a change in the status field

    if @nvchNewStatus <> @nvchOldStatus and @nvchDocType ='O' and @nvchNewStatus <> '0'
	select @nvchOrdNum = SalesOrder from inserted
	select @nvchCust = Customer from inserted
	select @nvchUser = LastOperator from inserted
	SET @nvchText = 'Order Number: ' + @nvchOrdNum + ' For Customer: ' + @nvchCust + ' Released from Suspense by: ' + @nvchUser

    begin
        if @nvchOldStatus ='S'
        begin
            -- send your email

Open in new window

HKFueyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can use UPDATE() to see if a particular column is actually updated. For example, UPDATE([status]) will be True if the column "status" has been updated, and False if it has been unchanged.
 
See https://msdn.microsoft.com/en-us/library/ms187326.aspx for more information.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, the trigger will fire on all updates, so you will have to determine what changed like you have in your code.

You can tell if a column changed before you go get the data from inserted, deleted by using:

IF UPDATE(OrderStatus)

If the OrderStatus changed then you can get the values to check to see if it was S and if it was then it meets your criteria.
0
HKFueyAuthor Commented:
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"
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You need to have another column, which tracks the last non-zero OrderStatus.

Or have another table, which tracks the full history of all orders.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
If OrderStatus begins with S that is an Insert not an Update.

If you create a trigger on Update for that table and you check to see if OrderStatus is UPDATE() then you can interrogate the change and see if the value of OrderStatus is still S

IF UPDATE(OrderStatus)
  BEGIN
    DECLARE @oldOrderStatus varchar(5), @newOrderStatus varchar(5)
    SELECT @oldOrderStatus = OrderStatus FROM deleted;
    SELECT @newOrderStatus = OrderStatus FROM inserted;
    IF @newOrderStatus <> 'S'
      BEGIN 
              -- Order Status Changed
              -- Do something with @newOrderStatus
      END
  END

Open in new window

0
ste5anSenior DeveloperCommented:
Caveat:

The UPDATE() function tells you only, whether a column is part of the affected row set. It does not  necessarily mean that the data has changed. E.g. UPDATE table_name SET column_name = column_name; will touch every row, UPDATE(column_name) will return TRUE, but no value has actually changed.

Triggers are executed per statement. Thus you need to work on sets. E.g.

IF UPDATE(OrderStatus)
    BEGIN
        UPDATE  OS
        SET     column_name = new_value_with_case_expression
        FROM    your_table T
                INNER JOIN DELETED D ON T.candidate_keys = D.candidate_keys
                INNER JOIN INSERTED I ON T.candidate_keys = I.candidate_keys
        WHERE   D.OrderStatus != I.OrderStatus;
    END;

Open in new window


Cause it's about sending of e-mail:

I don't like to tigger this in a trigger. Cause sending an e-mail and recieiving it is a non-deterministic process. So create an e-mail queue table, where you insert one row per e-mail to send. Use a SQL Server Agent Job to send those (e.g. a minute interval).
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
ste5anSenior DeveloperCommented:
@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

Open in new window

0
DBAduck - Ben MillerPrincipal ConsultantCommented:
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?
0
ste5anSenior DeveloperCommented:
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

Open in new window


Your trigger ignores that and will work with some arbitrary values from the virtual tables DELETED and INSERTED.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Mine was only an example, but you are right it is very possible that the deleted and inserted tables contain more than 1 row.
0
ste5anSenior DeveloperCommented:
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...
0
Scott PletcherSenior DBACommented:
CREATE TRIGGER trigger_name
ON table_name
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(OrderStatus)
BEGIN
    DECLARE @release_messages varchar(8000)
    SET @release_messages = ''
    SELECT @release_messages = @release_messages + '; ' +
        'Order Number: ' + CAST(i.SalesOrder AS varchar(10)) +
        ' For Customer: ' + CAST(i.Customer AS varchar(16)) +
        ' Released from Suspense by: ' + CAST(i.LastOperator AS varchar(50))
    FROM inserted i
    INNER JOIN deleted d ON
        d.SalesOrder = i.SalesOrder --!!chg JOIN conditions if needed!!
    WHERE
        d.OrderStatus = 'S' AND --previous OrderStatus was 'S'
        i.OrderStatus <> 'S' AND --current OrderStatus is not 'S'
        i.OrderType = 'O'
    SET @release_messages = STUFF(@release_messages, 1, 2, '')
    --!!send your email
END --IF
GO
0
Anthony PerkinsCommented:
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.
0
HKFueyAuthor Commented:
Thanks everyone for trying to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.