Create one record from multiple rows in a trigger

Question

Create one record from multiple rows in a trigger

132 Views Asked by Jeremy Reynolds At 04 April 2025 at 23:42

I have a trigger that fires on insert of a record. The application modifies the bank record of an employee but creates four records every time regardless of whether just one field is updated or inserted

For example you update the account number of an employee. The application will insert four rows into the audit table comprising of a record type account number, account type, bank and branch. Each of these inserted rows have an old value and a new value.

The data looks something like this:

Auditfieldid, recordtype, old value, new value
----------------------------------------------
1, account number, 99, 88
1, account type, C, A
1, bank, BOA, Regions
1, branch, Cedar Bluff, Clinton

I want the trigger to loop through the the inserted records, but build a single row in a temp table comprising:

Auditfieldid, AccountNoOldvalue, AccountNoNewValue, AccountTypeOldValue,
  AccountTypeNewValue, BankOldValue, BankNewValue, BranchOldValue, BranchNewValue
---------------------------------------------------------------------------------
1, 99, 88, C, A, BOA, Regions, Cedar Bluff, Clinton

The data above then gets represented in a HTML email. If I don't do the above, 4 emails get sent as a notification and I only want one with all the values represented.

This is what I have so far, works but generates 4 emails:

USE [SageStaging]
GO

/****** Object:  Trigger [MASSMART].[Vip_BankChange_Email]    Script Date: 2017-08-07 11:49:06 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--USE [SageStaging]
--GO

/****** Object:  Trigger [MASSMART].[Vip_BankChange_Email]    Script Date: 2017-07-12 08:36:41 AM ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO
--/------ 

ALTER TRIGGER [MASSMART].[Vip_BankChange_Email] 
ON [MASSMART].[MM_BankAudit] 
AFTER UPDATE
AS
 SET NOCOUNT ON;

DECLARE @AUDITFIELDID NVARCHAR(500)
DECLARE @EFFECTIVEDATE VARCHAR(500)
DECLARE @VIPUSERNAME VARCHAR(500)
DECLARE @SOURCECODE VARCHAR(500)
DECLARE @ACTIONSDESCRIPTION VARCHAR(500)
DECLARE @FIELDNAME VARCHAR(500)
DECLARE @TABLEFRIENDLYNAME VARCHAR(500)
DECLARE @OLDVALUE NVARCHAR(500)
DECLARE @AUDITVALUE NVARCHAR(500)
DEClARE @COMPANYRULE NVARCHAR(MAX)
DECLARE @Mail_Profile_Name VARCHAR(100)
DECLARE @SENT VARCHAR(10)
SET @Mail_Profile_Name = 'Sendmail'
DECLARE @MessageBody VARCHAR(MAX)
DECLARE @MailSubject NVARCHAR(500)
DECLARE @@CC VARCHAR(50)
DECLARE @@Sendto NVARCHAR(100)
DECLARE @COMPANYRULEID NVARCHAR(100)
DECLARE @NUMKEY NVARCHAR(100)


DECLARE Email_cursor CURSOR FOR

SELECT AUDITFIELDID, 
       companyruleid, 
       effectivedate, 
       VIPUserName, 
       SourceCode, 
       ActionsDescription, 
       FieldName, 
       OldValue, 
       AuditValue, 
       CompanyRule, 
       Sent, 
       numkey  
  FROM inserted


OPEN Email_cursor;

FETCH NEXT FROM Email_cursor INTO @AUDITFIELDID, @companyruleid, @effectivedate, @VIPUserName, @SourceCode, @ActionsDescription, @FieldName, @OldValue, @AuditValue, @CompanyRule, @sent, @numkey 


WHILE @@FETCH_STATUS = 0
BEGIN

SET @MailSubject = 'Banking Details Change Notification for Employee' + ' ' + @SOURCECODE
SET @MessageBody = '<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="content-type">
  <title></title>
</head>
<body>
<br>
The following bank details have been changed: 
<br>
<br>
 Date Changed:    ' + @EFFECTIVEDATE + '<br>' +
' Username:    ' + @VIPUSERNAME + '<br>' +
' Employee Details:    ' + @SOURCECODE + '<br>' +
' Action:    ' + @ACTIONSDESCRIPTION + '<br>' +
' Field:    ' + @FIELDNAME + '<br>' +
' Old Value:    ' + @OLDVALUE + '<br>' +
' New Value:    ' + @AUDITVALUE + '<br>' +
' Company:    ' + @COMPANYRULE + '<br>
<br>
<b>
Please do not respond to this email. If you have any questions regarding this email, please
contact your payroll administrator <br>
<br>
<br>
</body>
</html>'


-- Massmart Employers (Exec)
IF @COMPANYRULEID IN (36)
BEGIN
SET @@Sendto = '[email protected]'
END

-- Massmart Employers

IF @COMPANYRULEID IN (40,1,35,44)
BEGIN
SET @@Sendto = '[email protected]'
END



-- Cambridge Employers
IF @COMPANYRULEID IN (104,105,51,52,54,55,56,57)
BEGIN
SET @@Sendto = '[email protected]'
END

-- Builders Employers

IF @COMPANYRULEID IN (101,102,12,13,14,15,16,17,18,19,20,98)
BEGIN
SET @@Sendto = '[email protected]'
END

-- MDD Employers

IF @COMPANYRULEID IN (106,107,108,2,21,26,27,29,3,30,31,33,34,4,5,6,72,80,83,94,86,9,95,96,97,99)
BEGIN
SET @@Sendto = '[email protected]'
END

-- Unison

IF @COMPANYRULEID IN (37,39)
BEGIN
SET @@Sendto = '[email protected];[email protected];[email protected]'
END

-- FruitSpot

IF @COMPANYRULEID IN (46)
BEGIN
SET @@Sendto = '[email protected]'
END

-- Masscash

IF @COMPANYRULEID IN (103,58,60,63,64,65,66,68,69,79,81,85,89,90,91,92,93,94)
BEGIN
SET @@Sendto = '[email protected]'
END

-- Makro Employers

IF @COMPANYRULEID IN (70,47,48,50,78)
BEGIN
SET @@Sendto = '[email protected]'
END




EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Mail_Profile_Name,
@recipients = @@Sendto,
@body = @MessageBody,
@subject = @MailSubject,
@body_format = 'HTML'




FETCH NEXT FROM Email_cursor INTO @AUDITFIELDID, @COMPANYRULEID, @effectivedate, @VIPUserName, @SourceCode, @ActionsDescription, @FieldName, @OldValue, @AuditValue, @CompanyRule, @sent 

END


CLOSE Email_cursor
DEALLOCATE Email_cursor

Original Q&A

There are 1 best solutions below

**Alberto Martinez** · Answer 1

Depending on how the data is inserted, the trigger can fire only one time with four rows in Inserted, or four times with one row in Inserted each time.

First case, the trigger only fires once

This is the easy case, you can use a SELECT like the following one for condensing all the values in one row:

select ID,
  (select OldValue from Inserted
   where recordtype='account number' and ID=Temp.ID) as AccountNoOldvalue,
  (select NewValue from Inserted
   where recordtype='account number' and ID=Temp.ID) as AccountNoNewValue,
  (select OldValue from Inserted
   where recordtype='account type' and ID=Temp.ID) as AccountTypeOldValue,
  (select NewValue from Inserted
   where recordtype='account type' and ID=Temp.ID) as AccountTypeNewValue,
  (select OldValue from Inserted
   where recordtype='bank' and ID=Temp.ID) as BankOldValue,
  (select NewValue from Inserted
   where recordtype='bank' and ID=Temp.ID) as BankNewValue,
  (select OldValue from Inserted
   where recordtype='branch' and ID=Temp.ID) as BranchOldValue,
  (select NewValue from Inserted
   where recordtype='branch' and ID=Temp.ID) as BranchNewValue
from Inserted as Temp
group by ID

Second case, the triggers fires for each record

In this case, as noted in the comments of the question you can't get all the values at the same time inside the trigger. I would add add another audit table to the database, populate it from the trigger, and when the four type of changes are filled for a given ID then send the email from a second trigger on the new table:

create table AuditCondensed (
  ID int,
  AccountNoOldValue varchar(100),
  AccountNoNewValue varchar(100),
  AccountTypeOldValue varchar(100),
  AccountTypeNewValue varchar(100),
  BankOldValue varchar(100),
  BankNewValue varchar(100),
  BranchOldValue varchar(100),
  BranchNewValue varchar(100)
)
go

create trigger trFillAuditCondensed on YourCurrentAuditTable
after insert as
  declare @ID int, @RecordType char(25)

  select @ID=ID, @RecordType=RecordType from Inserted

  if not exists(select * from AuditCondensed where ID=@ID)
    insert into AuditCondensed (ID) select ID from Inserted

  if @RecordType='account number'
    update AuditCondensed set AccountNoOldvalue=OldValue,
      AccountNoNewValue=NewValue
    from AuditCondensed
    join Inserted on Inserted.ID=AuditCondensed.ID
  if @RecordType='account type'
    update AuditCondensed set AccountTypeOldValue=OldValue,
      AccountTypeNewValue=NewValue
    from AuditCondensed
    join Inserted on Inserted.ID=AuditCondensed.ID
  if @RecordType='bank'
    update AuditCondensed set BankOldValue=OldValue,
      BankNewValue=NewValue
    from AuditCondensed
    join Inserted on Inserted.ID=AuditCondensed.ID
  if @RecordType='branch'
    update AuditCondensed set BranchOldValue=OldValue,
      BranchNewValue=NewValue
    from AuditCondensed
    join Inserted on Inserted.ID=AuditCondensed.ID
go


create trigger trSendEmail on AuditCondensed
after update as
  if exists (select * from Inserted
   where AccountNoNewValue is not null
     and AccountTypeNewValue is not null
     and BankNewValue is not null
     and BranchNewValue is not null)
  begin
    -- sent your email here
  end
go

Create one record from multiple rows in a trigger

There are 1 best solutions below

First case, the trigger only fires once

Second case, the triggers fires for each record

Related Questions in SQL-SERVER

Related Questions in T-SQL

Related Questions in DATABASE-TRIGGER

Trending Questions

Popular # Hahtags

Popular Questions