Trigger to put the change date and time in the LastModified column of the Order table whenever an order item in SaleOrderDetail is changed

Question

I created table as follows:

CREATE TABLE Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerLName VARCHAR(30),
    CustomerFName VARCHAR(30)
);

CREATE TABLE SaleOrder
(
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT REFERENCES Customer(CustomerID),
    OrderDate DATE,
    LastModified datetime
);

CREATE TABLE SaleOrderDetail
( 
    OrderID INT REFERENCES SaleOrder(OrderID),
    ProductID INT,
    Quantity INT,
    UnitPrice INT,
    PRIMARY KEY (OrderID, ProductID)
);

I need to verify if this query is correct or what changes are required.

CREATE TRIGGER [dbo].[tr_Modify]
ON dbo.SaleOrder
AFTER Update
AS
BEGIN
    SET NOCOUNT on;

    IF UPDATE (OrderID)
    BEGIN
        UPDATE SaleOrder
        SET LastModified = GETDATE()
        FROM SaleOrder o
        INNER JOIN SaleOrderDetail od ON o.OrderID = od.OrderID
        WHERE o.OrderID <> od.OrderID
    END
END

Answer 1

I think that the syntax is correct but since OrderID is a Identity PK it cannot be updated so that trigger will never fire.

If your trying to update last modified in salesOrder from a update on SaleOrderDetail the following should do it:

CREATE TRIGGER [dbo].[tr_Modify2]
ON dbo.SaleOrderDetail
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT on;

    BEGIN
        UPDATE SaleOrder
        SET LastModified = GETDATE()
        FROM
            SaleOrder o
            INNER JOIN INSERTED INS ON INS.orderid = o.orderid

    END
END