SQL Trigger not Ensemblist approach issue

SQL Server defined triggers can be a pitfall for classic developers if they do not approach the trigger from an ensemblist (set-based querying ) point of view.

Let me show you an example

The origin

We have a table that handles project Information.
This table stores the project Name ([Name] field), and different Languages available for the Project ([Languages] field). Different Languages are Comma separated. The 1st Language in the list is the default one.

With this kind of data:

This structure does not allow us to set different project name descriptions for different Languages.
We updated the structure as follows:

We added a 'ProjectLanguages' Table. It is linked to the Project Table, and will be able to store the ProjectID and different Project Name descriptions for different Languages.

We created a script to fill the ProjectLanguages table with data available in Project table.
Here are is the result:

Because we have clients that access to Projects table to get information about Project, we need to keep the old structure synchronized with the new one.
We create a trigger on the ProjectLanguages table to do that.

The Test

We created two scripts to test it:

The 1st one inserts 4 new ProjectLanguages rows 2 for project 200 (ID = 4205) and 2 for project 201 (ID = 4206), and then removes them.
You should note that the test will execute 1 query for each action (4 insert queries & 4 delete queries)
In the test query we use a select statement to check the data status

Before adding item

After adding item

After deleting item

/** Tests Script row by row **/
                       
                      --Show Original Values
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)
                       
                      -- Insert New language for Project 200 & 201 (1 query for each row to add)
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                   (4205, 'PL','Test PL',0)
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                   (4205, 'NL','Test NL',0)
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                   (4206, 'PL','Test PL',0)                            
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                  (4206, 'NL','Test NL',0)                            
                       
                      --Show Values After Insert
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)
                       
                      -- Deleted Them  (1 query for each row to remove)
                      DELETE FROM ProjectLanguages
                          WHERE (ProjectID = 4205 AND ISOCode = 'PL')
                      DELETE FROM ProjectLanguages
                          WHERE (ProjectID = 4205 AND ISOCode = 'NL')   
                      DELETE FROM ProjectLanguages
                          WHERE (ProjectID = 4206 AND ISOCode = 'PL')
                      DELETE FROM ProjectLanguages
                          WHERE (ProjectID = 4206 AND ISOCode = 'NL')
                       
                      --Show Values After Delete
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)

Open in new window

The 2nd test will do exactly the same as the 1st one, but instead of creating 1 query for each row to add or remove, we will use ensemblistic approach and create 2 rows in only one query, and delete the 4 rows in 1 query.

/** Tests Script Ensemblistic **/
                       
                      --Show Original Values
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)
                       
                      -- Insert New 2 language for Project 200 & 201 (row 4205 & 4206 in Project Table)
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                   (4205, 'PL','Test',0)
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                   (4206, 'PL','Test',0)   
                      INSERT INTO ProjectLanguages (ProjectID, ISOCode, ProjectName, IsDefault)VALUES
                                                    (4205, 'NL','Test NL',0)
                                                   ,(4206, 'NL','Test NL',0)
                       
                      --Show Values After Insert
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)
                       
                      -- Deleted Them   
                      DELETE FROM ProjectLanguages
                          WHERE (ProjectID in (4205, 4206) AND (ISOCode = 'PL' OR ISOCode = 'NL'))
                         
                      --Show Values After Delete
                      SELECT [ID]
                            ,[ProjectID]
                            ,[Name]
                            ,[Languages]
                          FROM [dbo].[Projects] where ProjectID IN (200    , 201)

Open in new window

The expected result should be as follows:

After Updating we can see the PL and NL languages are correctly added to the Language Field, and after they are deleted they are removed.

Non Ensemblistic Trigger approach

We created 2 triggers. 1 for Insert and Update

ON dbo.ProjectLanguages
                      AFTER INSERT, UPDATE
                      AS
                      BEGIN
                       
                          DECLARE @languagesString NVARCHAR(32)
                          DECLARE @projectID INT
                          DECLARE @defaultProjectName NVARCHAR(150)
                         
                          SELECT @projectID = i.ProjectID FROM inserted i
                          SELECT @defaultProjectName = ProjectName FROM dbo.ProjectLanguages WHERE ProjectID =  @projectID and IsDefault = 1
                         
                          SELECT @languagesString=
                              replace(replace(replace(
                                  (
                                      SELECT ISOCode
                                      FROM dbo.ProjectLanguages
                                      WHERE ProjectID = @projectID
                                      ORDER BY isdefault DESC
                                      FOR xml raw
                                  )
                               ,'"/><row ISOCode="',',')
                               ,'<row ISOCode="','')
                               ,'"/>','')
                              --as ISOList
                         
                          UPDATE dbo.Projects SET
                               Languages = @languagesString
                              ,Name = @defaultProjectName
                          WHERE
                              ID = @projectID
                      END
                      GO

Open in new window

and another for delete

CREATE TRIGGER RecordDeletion
                      ON dbo.ProjectLanguages
                      AFTER DELETE
                      AS
                      BEGIN
                       
                          DECLARE @languagesString NVARCHAR(32)
                          DECLARE @projectID INT
                          DECLARE @defaultProjectName NVARCHAR(150)
                         
                          SELECT @projectID = i.ProjectID FROM DELETED i
                          SELECT @defaultProjectName = ISNULL((SELECT ProjectName FROM dbo.ProjectLanguages WHERE ProjectID =  @projectID and IsDefault = 1),'')
                          
                          SELECT @languagesString=
                              replace(replace(replace(
                                  (
                                      SELECT ISOCode
                                      FROM dbo.ProjectLanguages
                                      WHERE ProjectID = @projectID
                                      ORDER BY isdefault DESC
                                      FOR xml raw
                                  )
                               ,'"/><row ISOCode="',',')
                               ,'<row ISOCode="','')
                               ,'"/>','')
                              --as ISOList
                         
                          UPDATE dbo.Projects SET
                               Languages = @languagesString
                              ,Name = @defaultProjectName
                          WHERE
                              ID = @projectID
                      END
                      GO

Open in new window

We used the following approach in these scripts:

Get the Impacted Project ID : @projectID"]

Using @projectID to retrieve all Languages associated with this project and set @languagesString with concatenated ISOCode"]

Update Projects table setting Languages = @languagesString and Name = @defaultProjectName where ID = (@projectID)]

Now let’s test it:

As we can see, the result is correct. The NL & PL languages are correctly added to the Languages field, and correctly remove when it was deleted.

Let's try a second test:

But this time the result is not what we expected.
For project 200, the Insert steps added NL & PL correctly, but in project 201 only PL was added and NL is missing.
And in project 200, the Delete step removed PL and NL correctly but in project 201 PL is not removed.

If we analyze the insert step, we can see that single row query worked(PL Language) but multi-row query (NL Language) did not work for the second project.

Analyzing the Delete step to see what happen for project 201, you can see the problem comes from this line:

SELECT @projectID = i.ProjectID FROM inserted i

Open in new window

In this line we get the impacted project ID. That works great if we insert only 1 row, but what happens if we insert 2 rows at the same time?
A sequential developer will think that the trigger will be fired 2 times, but that's not the case. The trigger will be fired only 1 time, and in the inserted table we will have 2 rows. Unfortunately in this case @projectID will be set with the i.ProjectID of the 1st row.
That’s why NL is added only for the project 200. Because it's the 1st item in the Inserted table it will be updated, but in project 201 it is missing.

In the deletion step, we have the same issue with this line:

SELECT @projectID = i.ProjectID FROM DELETED i

Open in new window

The deleted table will contain 4 rows and only the 1st will be used to set @projectID variable.

Ensemblistic Approach

Now the issue is how to fix this?
We need to change our approach and work with effected rows instead of the table:
1st create a table of impacted projects.
2nd Update data using impacted Project Table

CREATE TRIGGER ProjectLanguageSyncTrigger
                          ON dbo.ProjectLanguages
                          FOR INSERT, UPDATE, DELETE
                          AS
                      
                      -- scenario 1
                      -- deleted but no insert might mean delete projects and scenario 3 might still apply
                      -- Not included in this example
                      
                      -- scenario 2
                      -- insert or deleted and exists in projects s/b update projects
                      
                              DECLARE @impactedProjectID TABLE (
                                                                ProjectID INT
                                                               )
                              --Get impacted project on UPDATE or INSERT Statement
                              INSERT INTO @impactedProjectID(
                                                              ProjectID
                                                             )
                                                     SELECT ProjectID
                                                     FROM (
                                                             SELECT ProjectID
                                                               FROM Inserted
                                                             group by ProjectID
                                                          UNION ALL
                                                            SELECT ProjectID
                                                              FROM Deleted
                                                             group by ProjectID
                                                      ) as T
                                                     group by ProjectID
                                  
                              UPDATE Projects SET
                                   Languages = (SELECT
                                                  REPLACE(REPLACE(REPLACE(
                                                  (
                                                      SELECT ProjectLanguages.ISOCode
                                                          FROM dbo.ProjectLanguages as ProjectLanguages
                                                          WHERE ProjectLanguages.ProjectID = Projects.ID
                                                          ORDER BY ProjectLanguages.isdefault DESC
                                                          FOR xml raw
                                                  )
                                               ,'"/><row ISOCode="',',')
                                               ,'<row ISOCode="','')
                                               ,'"/>',''))
                                  ,Name = ISNULL((SELECT ProjectLanguages.ProjectName
                                                      FROM dbo.ProjectLanguages as ProjectLanguages
                                                      WHERE ProjectLanguages.ProjectID =  Projects.ID
                                                          AND ProjectLanguages.IsDefault = 1)
                                                  ,'')
                              FROM dbo.Projects AS Projects
                                  INNER JOIN @impactedProjectID as i
                                      ON Projects.ID = i.ProjectID
                      
                      -- scenario 3
                      -- inserted or deleted and not exists in projects s/b insert projects from projectlanguages
                      -- Not included in this example
                            
                      GO

Open in new window

and let see the test results:

That worked.

Note that the inserted and deleted tables always exist in the trigger context.

Thanks to Mark Cooper to take time to review this article.

Comments (1)

Racim BOUDJAKDJI

Database Architect - Dba - Data Scientist

CERTIFIED EXPERT

Commented: 2014-06-04

Thanks for this proof of concept (Do me a favor and send it to MS).

Indeed, triggers are procedural-inspired row constructors so it should be no surprise their behavior may create update anomalies as well as performance problems since the optimizer won't be able to do hashing or merging sets...

All triggers are to be replaced by declarative constraints whenever possible...

Regards...