<

SQL Trigger not Ensemblist approach issue

Published on
7,478 Points
1,478 Views
Last Modified:
Approved
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.
Original Schema
With this kind of data:
Original Datas
This structure does not allow us to set different project name descriptions for different Languages.
We updated the structure as follows:
 
New Database Schema
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:
 
Prepared Data
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:
 
Expected result
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:
Trigger 1 Test 1
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:
 
Trigger 1 Test 2
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:
 
Trigger 2 Test 1Trigger 2 Test 2
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.
0
Comment
1 Comment
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
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...
0

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month