SQL Server - Creating a trigger to send email after updating a column

Question

I'm creating a database for a rentals system for musical instruments where I want an email sent to the administrator when the residual value of an instrument is zero or less than zero. An earlier stored procedure updates this tables column instrument_residual_value when a payment is recorded in the Payments table.

The code I have is:

CREATE TRIGGER notify_balance
ON instrument
AFTER UPDATE 
AS 
BEGIN   
    SET NOCOUNT ON;

    IF NEW.instrument_residual_value <= 0 
        EXEC .msdb.dbo.sp_send_dbmail
              @profile_name = 'manager',
              @recipients = '[email protected]',
              @subject = 'This instrument is paid off',
              @body = CONCAT('The residual value on ', [NEW.instrument_name], 'is now zero.');
END;

I'm getting a syntax error ostensibly in the @body = CONCAT line but I suspect that the keyword NEW is the issue as removing NEW.instrument_name from that line shifts the error to

IF NEW.instrument_residual_value <= 0 

Is my code OK here or is there a better way of doing this?

Using SQL Server 2022 Express.

After reading marc_s' comment my code now reads

CREATE TRIGGER dbo.notify_balance_zero   
   ON  dbo.instrument 
   AFTER UPDATE
AS 

BEGIN        
    SET NOCOUNT ON;

    DECLARE @instrument VARCHAR(MAX) = (SELECT INSERTED.instrument_name 
                                         FROM INSERTED 
                                         WHERE INSERTED.instrument_residual_value <= 0 )
                                         
    IF @instrument IS NOT NULL
        BEGIN
            EXEC .msdb.dbo.sp_send_dbmail
                @profile_name = 'manager',
                @recipients = '[email protected]',
                @subject = 'This instrument is paid off',
                @body = CONCAT('The residual value on ', [INSERTED.instrument_name], 'is now zero.');
        END

END
GO

Is this valid ads I still get an error in the @body line?

Answer 1

The problem appears to be that neither the CONCAT nor COALESCE statement is acceptable if you're creating a trigger. The following code at least ran and created the trigger.

   CREATE TRIGGER notify_balance_zero 
   ON  instrument 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
        DECLARE @instrument_name VARCHAR(MAX) = (
        SELECT INSERTED.instrument_name  
        FROM INSERTED 
        WHERE INSERTED.instrument_residual_value <= 0 )
        EXEC .msdb.dbo.sp_send_dbmail
            @recipients = '[email protected]'
            ,@subject = @instrument_name
            ,@body = 'The balance on this instrument is now Zero, (or less)'
;
END
GO

Don't know if it works for real yet though.