Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HKFuey

ASKER

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"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open 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

Open 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...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of HKFuey

ASKER

Thanks everyone for trying to help