Trigger on 1 column update

Question

I am creating a trigger on a table to run a stored proc only when a specific column is updated. Below is my trigger code but when I try to run UPDATE to that column I get "Error converting data type nvarchar to smalldatetime. The statement has been terminated." The column named OriginalTargetDate is a smalldatetime data type.

USE [Marshall]
GO

/****** Object:  Trigger [dbo].[T_RepairOrder_DTrig]    Script Date: 12/22/2021 9:34:04 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER TRIGGER [dbo].[T_RepairOrder_UTDDTrig] 
ON [dbo].[RepairOrder]
FOR UPDATE AS
BEGIN

    IF ( UPDATE (OriginalTargetDate) )
        BEGIN
            EXEC ms_updTDDTime OriginalTargetDate;
        END
END

Answer 1

To send the new value, try something like this:

. . .
declare @d smalldatetime = (select top(1) OriginalTargetDate from inserted)
EXEC ms_updTDDTime @d
. . .

However you should probably redesign the trigger to support multiple updates (when several rows are changed).

Answer 2

Thanks everyone! All of your feedback helped me find a solution.

Answer 3

This should get you started:
CREATE OR ALTER TRIGGER [dbo].[T_RepairOrder_UTDDTrig]
ON [dbo].[RepairOrder]
FOR UPDATE AS
BEGIN
IF ( UPDATE (OriginalTargetDate) )
BEGIN
DECLARE @newdate datetime;
DECLARE update_cursor CURSOR FOR
SELECT DISTINCT i.OriginalTargetDate -- updated value
FROM INSERTED i
INNER JOIN DELETED d
ON d.RepairOrderPK = i.RepairOrderPK
WHERE i.OriginalTargetDate <> d.OriginalTargetDate
OPEN update_cursor;
FETCH NEXT FROM update_cursor INTO @newdate;
WHILE @@Fetch _STATUS=0
BEGIN
EXEC ms_updTDDTime @newdate;
FETCH NEXT FROM update_cursor INTO @newdate;
END
CLOSE update_cursor;
DEALLOCATE update_cursor;
END
END