Trigger send email on new record

[RJL1]

Hello,

I'm working on the following trigger which should send an email when a new record is created. The new record is created when a shipment is back order.

I'm new to triggers so I'm not sure this is correct but whe nI try to create the trigger I get the following error

Incorrect syntax near '@SHIPMENT_ID'.
Must declare the scalar variable "@SHIPMENT_ID".

CREATE TRIGGER WEPK_BACKORDER_EMAIL
 ON WEPK_SHIPMENT_BACKORDER
 AFTER INSERT

AS
BEGIN

@SHIPMENT_ID NVARCHAR(25),
@BODY VARCHAR(500)

SELECT @SHIPMENT_ID = I.SHIPMENT_ID

FROM INSERTED I

SET @BODY = 'Shipment Number ' @SHIPMENT_ID ' has been back order'

EXEC MASTER.XP_SENDMAIL
 @RECIPIENTS = 'user@domain.com,
 @SUBJECT = 'Shipment Backorder',
 @MESSAGE = 'Shipment Number '@SHIPMENT_ID' has been back order'

END

Any help with this is appreciated
RJL1

 

[markros]

This trigger has several major problems.

I suggest you to read this interesting blog first

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/t-sql-tuesday-21-a

and then also

http://blogs.lessthandot.com/index....rver/best-practice-coding-sql-server-triggers

and then ask if you still have questions.

PluralSight Learning Library

 

[RJL1]

Thanks markros, as alway very good feedback. Articles were great and based on them I took a different approach and I have the triogger to write to the new table done and will tackle the email in a different way (with a schedule task)

Thanks