Multiple IF statements in Delete Trigger

Question

I'm trying to create the following logic in a SQL Server trigger:

1. Check if Deleted.ProfID = 3
2. If Yes, check if there are not any records in tblPartLic with ProfID = 3, then
3. If not, Insert into tblParticipantLicense a not licensed 3 record
   -- a. idsNumber, txtEmailAddress, state = 70 state

I tried:

IF (deleted.idsProf = 3)
BEGIN

    IF NOT Exists(Select PL.idsNumber FROM tblPartLic PL INNER JOIN Deleted D 
        ON PL.idsNumber=D.idsNumber 
        WHERE PL.idsProfession = 3 AND PL.idsState <> 70)
    BEGIN

        INSERT INTO tblPartLic (idsNumber, txtLicNumber, txtState, txtProfOrg, 
                idsCountry, idsState, idsProf)
            SELECT Deleted.idsDASNumber, 'AR NL', 'NL', 'Architect', 208, 70, 3
            FROM Deleted 

    END;
END;            

Answer 1

Deleted is a table (pseudo-table) and as such could have multiple rows - some of which may have ProfID = 3 and some of which may have ProfId <> 3. This must be handled. Also relational databases are designed for set based operations, not procedural ones. Your logic can be built into a single insert statement with the appropriate where clause. The following reproduces what you have shown above:

INSERT INTO tblPartLic (idsNumber, txtLicNumber, txtState, txtProfOrg, idsCountry, idsState, idsProf)
    SELECT D.idsDASNumber, 'AR NL', 'NL', 'Architect', 208, 70, 3
    FROM Deleted D
    -- Condition 1 - ProfID = 3
    WHERE D.ProfID = 3
    -- Condition 2 - No existing record in tblPartLic
    AND NOT EXISTS (
        SELECT PL.idsNumber
        FROM tblPartLic PL
        WHERE PL.idsProfession = 3 AND PL.idsState <> 70
        AND PL.idsNumber = D.idsNumber
    );