Greetings,
I am having some trouble with a trigger. This is my first attempt at
creating a trigger so any help would be great. The function of the
trigger is, on the insert of a row to check the value inserted into to
column VEH_REPAIR_CODE. If that value is not 19, send an email to the
value inserted in column VEH_CREW_CK_LANID. The email would include
the values inserted from columns VEH_CREW_CK_LANID and
VEHICLE_CHECK_ID ( the primary key of the table so the value is
inserted from the system). If have the following syntax but it does
not seem to work.

Thank you for any help you can provide.

CREATE TRIGGER car_check_repair_insert ON dbo. VEHICLE_CHECK FOR
INSERT AS
BEGIN
DECLARE @CREW_1 VARCHAR(20)
DECLARE @UNIT_1 VARCHAR(20)
DECLARE @VEH_REPAIR_CODE INT

DECLARE @MCC_ID VARCHAR(10)
BEGIN
SELECT @VEH_REPAIR_CODE = INSERTED.VEH_REPAIR_CODE, @CREW_1 =
INSERTED.VEH_CREW_CK_LANID, @UNIT_1 = INSERTED.VEHICLE_NUM,@MCC_ID=
VEHICLE_CHECK_ID
From INSERTED
IF @VEH_REPAIR_CODE <> 19
BEGIN
--Send the email here
DECLARE @MAIL_TO VARCHAR(50)
DECLARE @AUNIT VARCHAR(150)
DECLARE @MCID VARCHAR(20)

SELECT @MAIL_TO = @CREW_1 + '@exrch.site.com',@AUNIT ='unit
number ' + @UNIT_1 + ' Reference number ' + @MCID '
exec master.dbo.xp_sendmail @recipients = @MAIL_TO,
@message = @AUNIT,
@subject = 'car check'


END
END

There are some issues you have to think of:

1=2E Triggers are fired per statement NOT per row so your code has to
handle the multiple rows case.
2=2E Triggers are not very nice in performance, thir are even BAD if you
are calling something like a mail sending routines which send mails
synchronisly, the code and the insert statement will hold until the
process is done with sending the mail (I assume that you don=B4t want to
use that in your code). See me considerations with the deep links on:

http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/=
thread/a5be89d521c96310
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/=
thread/f99983360fe14022
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse=
_frm/thread/27fbc670c00c7062

There are other ways to handle that like putting it in a interim table
to loop though the table at another time a job to send the mails.

HTH, jens Suessmeyer.

Anyway, I rewrote your code to reflect the multirow changes.



CREATE TRIGGER car_check_repair_insert ON dbo. VEHICLE_CHECK
FOR INSERT AS
BEGIN

DECLARE @CREW_1 VARCHAR(20)
DECLARE @UNIT_1 VARCHAR(20)
DECLARE @VEH_REPAIR_CODE INT
DECLARE @RowCount INT
DECLARE @I INT
DECLARE @RC INT


DECLARE @MAIL_TO VARCHAR(50)
DECLARE @AUNIT VARCHAR(150)
DECLARE @MCID VARCHAR(20)
DECLARE @MCC_ID VARCHAR(10)


--Cause there can be more than 1 row in the inserted tables you have to
capsulate that
SELECT IDENTITY(INT,1,1) AS RowCounter, VEHICLE_CHECK_ID
INTO #INSERTEDRows
FROM INSERTED

--Get the numbersof rows inserted
SELECT @ROWCOUNT =3D @@ROWCOUNT

WHILE @I < @ROWCOUNT
BEGIN


SELECT
@VEH_REPAIR_CODE =3D INSERTED.VEH_REPAIR_CODE,
@CREW_1 =3D INSERTED.VEH_CREW_CK_LANID,
@UNIT_1 =3D INSERTED.VEHICLE_NUM,
@MCC_ID=3D VEHICLE_CHECK_ID
[quoted text, click to view]
INNER JOIN
#INSERTEDRows ON
INSERTED.VEHICLE_CHECK_ID =3D INSERTED.VEHICLE_CHECK_ID
WHERE RowCounter =3D @I


IF @VEH_REPAIR_CODE <> 19
BEGIN
--Send the email here

SELECT @MAIL_TO =3D @CREW_1 + '...@exrch.site.com',@AUNIT =3D'unit
number ' + @UNIT_1 + ' Reference number ' + @MCID
exec @RC =3D master.dbo.xp_sendmail @recipients =3D @MAIL_TO,
@message =3D @AUNIT,
@subject =3D 'car check'

--Do Something with the return code (e.g. checking for the status and
doing something if anerror occured)

SET @I =3D @I +1=20

END=20
END=20
END