After Trigger with JOIN Tables

Question

TASK: Create an AFTER TRIGGER to accomplish a condition from a JOIN. The Trigger would be in table_1 when some record is created. Meanwhile, table_2 has a common column with some parameters that the condition needs to have.

Every time that the Result <> 1 AND Status <> 3 in table_2 and ALERT should be sent

-- QUERY WITH JOIN TABLE_1 ON TABLE_2

-- MOCK TABLE 

-- Table_1 as A  |    Table_2 as B

   A.LotCode | A.LineNumber  | B.Result  |  B.Status 

    00000    |   xxxx        |   1       |   3
    00001    |   xxxx        |   2       |   4

-- The LotCode 00001 should send it through email because satisfy the condition 

CREATE TRIGGER FullfillOrderQCResult
ON Table_1
AFTER INSERT
AS
BEGIN
      -----DECLARE VARIABLES-----

      DECLARE @LOTNUMBER VARCHAR(50)
      DECLARE @ACTIONPEFORMED VARCHAR(MAX)
      DECLARE @ITEM INT
      DECLARE @RESULT TINYINT
      DECLARE @STATUS TINYINT

      SELECT @LOTNUMBER = A.LotCode, @ITEM = A.LineNumber, @RESULT = B.Result, @STATUS = B.Status
      FROM inserted AS A
      JOIN Table_2 AS B
      ON A.LotCode = B.DocumentID2
    
      -----CONDITION WHEN I INSERT A VALUE-----

      IF (@RESULT <> 1 AND @STATUS <> 3)
      BEGIN     
          SET @ACTIONPEFORMED = 
              N'Hello, ' + '<br>' + '<br>'
              + N'  The following LOT NUMBER: ' + @LOTNUMBER + ' has not been approved for this Item: '  

          EXEC MSDB.DBO.SP_SEND_DBMAIL
            @PROFILE_NAME = 'SQLMail',
            @RECIPIENTS = 'TEST@gmail.com',
            @SUBJECT = 'LOT NON-Approved',
            @BODY = @ACTIONPEFORMED,
            @IMPORTANCE = 'HIGH',
            @BODY_FORMAT = 'HTML'
      END
      ELSE 
            PRINT 'ALL GOOD MY FRIEND'
END

TESTING THE TRIGGER

--------INSERT VALUES------------------

INSERT INTO Table_1 (LotCode,LineNumber)
values ('00000','xxxx')

-----EXISTING VALUES-----

INSERT INTO Table_2 (CreationUser,DocumentID1,DocumentID2,DocumentID3,Result,Status)
values ('JL','00000','00000','00000',2,3)

Answer 1

The following shows you how to handle the fact that Inserted might have multiple rows. This is really not ideal behaviour for a trigger, because you have to process the results RBAR (row by agonising row), which is slow by itself, let alone the fact that you are sending an email.

CREATE TRIGGER FullfillOrderQCResult
ON Table_1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -----DECLARE VARIABLES-----

    DECLARE @ACTIONPEFORMED varchar(max), @Id int;

    SELECT A.LotCode, A.LineNumber, CONVERT(bit, 0) Done, IDENTITY(int) id -- Use your own id if you have one, just need to uniquely identify each row.
    INTO #FullfillOrderQCResult_temp
    FROM Inserted AS A
    INNER JOIN Table_2 AS B ON A.LotCode = B.DocumentID2
    WHERE B.Result <> 1 and B.[Status] <> 3;

    WHILE EXISTS (SELECT 1 FROM #FullfillOrderQCResult_temp WHERE Done = 0) BEGIN
        SELECT TOP 1 @Id = id, @ACTIONPEFORMED = 
            N'Hello, ' + '<br>' + '<br>'
            + N'The following LOT NUMBER: ' + LotCode + ' has not been approved for this Item: ' + LineNumber
        FROM #FullfillOrderQCResult_temp
        WHERE Done = 0;

        EXEC MSDB.DBO.SP_SEND_DBMAIL
            @PROFILE_NAME = 'SQLMail',
            @RECIPIENTS = 'TEST@gmail.com',
            @SUBJECT = 'LOT NON-Approved',
            @BODY = @ACTIONPEFORMED,
            @IMPORTANCE = 'HIGH',
            @BODY_FORMAT = 'HTML';

        UPDATE #FullfillOrderQCResult_temp SET Done = 1 WHERE id = @Id;
    END;
END;

I don't know whether you would still want the concept of 'ALL GOOD MY FRIEND' because you could have none, some or all rows with issues. Anyway I assume print is only for debugging.

---

That said you would be much better off pushing an event into a queue and having a service process said event because triggers really should be as fast as possible. And adding an event to a queue could be handled in a set based manner e.g.

CREATE TRIGGER FullfillOrderQCResult
ON Table_1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MyEventQueue (A.LotCode, A.LineNumber) -- Any other information required to identify the records etc
        SELECT A.LotCode, A.LineNumber
        FROM Inserted AS A
        INNER JOIN Table_2 AS B ON A.LotCode = B.DocumentID2
        WHERE B.Result <> 1 and B.[Status] <> 3;
END;