How to read uncommitted data from other table inside trigger

Question

I have 2 tables, Contact and ContactFunction. They have a One to Many relationship, as in a Contact can have many ContactFunctions. So ContactFunction has ContactID as a foreign key. So in my .NetCore web application I can update a contact and their functions on the same page. But this is in fact saving two separate entities, named above. I have a trigger on the Contact table that puts inserts/updates/deletes into a history table. I need a way to also include any ContactFunction changes in this trigger too. But at this stage it seems there has been no commits yet to the ContactFunction table, because of the relationship I assume. Can you think of a way to do this other than creating a history table for both?

I'm trying to read the data from the ContactFunction table inside the Contact table trigger, but it's not there yet. the variable @FUNCTIONSSTRING is where I'm trying to read data from the second table (ContactFunction)

USE [DI_Business_IE_EF_Development04]
GO
/****** Object:  Trigger [dbo].[ContactHistoryLog]    Script Date: 25/01/2019 09:33:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ContactHistoryLog] ON [dbo].[Contact]
AFTER INSERT,UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CurrentDateTime datetime2 = SYSUTCDATETIME();
DECLARE @Action varchar(10);
DECLARE @CONTACTID INT;
DECLARE @FIRSTUPDATE INT;
DECLARE @FUNCTIONSSTRING VARCHAR(250) = '';
DECLARE @INSERTEDID INT;

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    SET @Action = 'UPDATE'
  END
  ELSE
  BEGIN
    SET @Action = 'INSERT'
  END
END
ELSE
BEGIN
  SET @Action = 'DELETE'
END

/* Update start times for newly updated data */
UPDATE c
SET
       StartDate = @CurrentDateTime, @CONTACTID = c.ID
FROM
    dbo.Contact c
    INNER JOIN inserted i
        ON c.Id = i.Id

SELECT @FIRSTUPDATE = COUNT(*) FROM dbo.ContactHistory WHERE ContactId = @CONTACTID

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT 
    @FUNCTIONSSTRING = @FUNCTIONSSTRING + F.Name + ','
    FROM ContactFunction CF
    JOIN [Function] F ON F.ID = CF.FunctionID
    WHERE CF.ContactID = @CONTACTID 


INSERT INTO DBO.ContactHistory(
ContactId,
CompanyID,
Title,
Forename,
Surname,
JobTitle,
ExecutiveNumber,
IsInformationProvider,
Email,
UpdatedBy,
StartDate,
EndDate,
[Action],
FunctionNames
)
SELECT
I.Id,
I.CompanyID,
I.Title,
I.Forename,
I.Surname,
I.JobTitle,
I.ExecutiveNumber,
I.IsInformationProvider,
I.Email,
I.UpdatedBy,
@CurrentDateTime,
@CurrentDateTime,
@Action,
(SELECT SUBSTRING(@FUNCTIONSSTRING, 0, LEN(@FUNCTIONSSTRING)))AS FunctionNames
FROM
       dbo.Contact c
       RIGHT JOIN inserted I
              ON c.Id = I.Id
-- if this is first update insert deleted value as well as inserted value, else just put in inserted
IF (@FIRSTUPDATE = 0)
BEGIN
INSERT INTO DBO.ContactHistory(
ContactId,
CompanyID,
Title,
Forename,
Surname,
JobTitle,
ExecutiveNumber,
IsInformationProvider,
Email,
UpdatedBy,
StartDate,
EndDate,
[Action],
FunctionNames
)
SELECT
d.Id,
d.CompanyID,
d.Title,
d.Forename,
d.Surname,
d.JobTitle,
d.ExecutiveNumber,
d.IsInformationProvider,
d.Email,
d.UpdatedBy,
d.StartDate,
ISNULL(d.StartDate,@CurrentDateTime),
'NONE',
(SELECT SUBSTRING(@FUNCTIONSSTRING, 0, LEN(@FUNCTIONSSTRING)))AS FunctionNames
FROM
       dbo.Contact c
       RIGHT JOIN deleted d
              ON c.Id = d.Id

END
END

I would expect to see the latest changes to the ContactFunction table, but it's not there. is there a way to do this?

Your trigger is really flawed, it assumes that your `INSERT` will only ever contain 1 row; it shouldn't. I also can't see where it handles a `DELETE` or `UPDATE` (generally I find it better to have different triggers for `INSERT`, `UPDATE` and `DELETE`, even though you *can* create one trigger that does all of them). Even if you *think* that your application will only ever `INSERT`/`UPDATE`/`DELETE` one row at a time, you should still never make the assumption that will always be true. Treat the data as a dataset; which means getting rid of syntax like `SELECT @Var = Col FROM inserted;`. — Thom A, Jan 25 '19 at 14:18
Hmm, silently changing the isolation level in a trigger can lead to a lot of funny, seemingly inexplicable behavior... — sticky bit, Jan 25 '19 at 14:21
@Lamu this is where my lack of SQL prowess shows. At the moment it will only over be one row, unless the application changes. I think perhaps I'm trying to attempt something that shouldn't be attempted? — Del Habington, Jan 25 '19 at 14:36
Don't get stuck in the trap of thinking it will only ever be a single row. The application **can** change. Or you may just need to import some data at some point. Making your triggers handles set is the only way to do this. Seems like the real issue is in how you are handling this in your code. If you need two tables data to be in synch it needs to be done in a single transaction. Having two different objects save portions of this is going to cause problems. — Sean Lange, Jan 25 '19 at 14:40
Something seems out-of-whack here. You define a trigger on a LOG table - which is very unusual. Typically one expects to see triggers on the tables that are being logged, not the log table. Worse, you have a log table of a HISTORY table. IME this is a very unusual setup. I think it's time to step back and think about what you are trying to accomplish because this approach makes little sense based on your table names. — SMor, Jan 25 '19 at 15:25
Yeah table names are probably misleading, think it's time to get back to the drawing board. Can't re-design database as another application relies on it too. Thanks for the comments guys — Del Habington, Jan 25 '19 at 15:40

How to read uncommitted data from other table inside trigger

0 Answers0