Wednesday, July 15, 2015

SQL Server DML Update Trigger for Multiple Rows

Background

Currently I am working on a tool creating cable labels according to some specification. In this tool  I need to perform a complex concatenation of values from about 10 columns depending on the fill status of these rows and update the label column with the concatenated value in SQL Server 2012. The concatenation should be performed in case of a single row update and of a multi-row update via UPDATE <table> SET col1=value1, ..., colN=valueN
The weapon of choice was a DML database trigger AFTER UPDATE. As I hard some hard time to achieve my goal I want to share the code with the community, maybe you can get something out of it for your own project.

Table Structure

The cable label consists mainly of location information of the start and end points of a cable (components/devices connected by the cable and the room where the device can be found). My table has a structure like this, with fields not required for the trigger operation omitted:
  • KeyField int, primary key
  • CONCAT_VALUE varchar(1000)
  • [...]
  • NUMBER int
  • START_COMP_ID varchar(100)
  • START_COMP_IS_NOMEN bit
  • START_AREA_ID varchar(50)
  • START_ROOM_NO varchar(200)
  • START_LEVEL_ID varchar(10)
  • START_BLD_ID varchar(50)
  • END_COMP_ID varchar(100)
  • END_COMP_IS NOMEN bit
  • END_AREA_ID varchar(50)
  • END_ROOM_NO varchar(200)
  • END_LEVEL_ID varchar(10)
  • END_BLD_ID varchar(50)
  • FUNCTION_ID varchar(10)
  • [...]

Database Trigger

The database trigger consists of two main parts: one executed if a single line has been changed (@@ROWCOUNT = 1, mostly for manual input in the DB editor) and one for multi-row update (@@ROWCOUNT > 1, e.g. in the case of UPDATE table SET <some column> = <new value>).
In the first case one can use IF statements checking if some column is NULL directly via selection by the primary key column. An example:
DECLARE @Buffer varchar(255) 
[...] 
IF NOT (SELECT START_COMP_ID FROM table WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
   @Buffer = <concatenation> 
[...]
UPDATE table SET CONCAT_VALUE = @Buffer WHERE KeyField =(SELECT KeyField FROM inserted); 
In case of multi-row updates I used an approach with DB cursor which loops over the INSERTED table. The IF statement has then to be based on the value coming from the cursor:
DELCARE @KeyField int
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR SELECT KeyField FROM inserted 
[...]
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO @KeyField
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    IF NOT (SELECT START_COMP_ID FROM table WHERE 
    KeyField = @KeyField) IS NULL
    [...] 
    UPDATE table SET CONCAT_VALUE = @Buffer WHERE              KeyField = @KeyField; 
    END 

Complete Code of Trigger

Here is the complete code of the trigger if you want to study it in detail:
USE [myDB]
GO
/****** Object:  Trigger [dbo].[TriggerName]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TriggerName] ON [dbo].[myTable] 
AFTER UPDATE 
AS 
DECLARE @START_COMP_ID varchar(100);
DECLARE @START_LOCATION varchar(255);
DECLARE @START_RACK varchar(255);
DECLARE @END_COMP_ID varchar(100);
DECLARE @END_LOCATION varchar(255);
DECLARE @END_RACK varchar(255);
DECLARE @FUNCTION varchar(255);
DECLARE @Buffer varchar(255);
DECLARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT KeyField FROM inserted

BEGIN
IF @@ROWCOUNT = 0
    RETURN
SET NOCOUNT ON;
SET @Buffer = '';

-- Execute only if one line has been changed
If (SELECT COUNT(*) FROM inserted) = 1
  BEGIN
-- ID part for component/technical place
    IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted) IS NULL
-- Component ID is not according to nomenclature
      IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))= 1
        SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + '>';
      ELSE
        SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KABEL_ID=(SELECT KABEL_ID FROM inserted));
    ELSE
    SET @START_COMP_ID = '';
    IF NOT (SELECT ACCNOMEN_FUNCTION FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @CONCAT_VALUE = '[' + (SELECT CONCAT_VALUE FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE
      SET @ACCNOMEN_FUNCTION = '[-';
    IF NOT (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @CONCAT_VALUE = @CONCAT_VALUE + '.' + (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + ']';
    ELSE
      SET @CONCAT_VALUE = @CONCAT_VALUE + ']';
-- ID part of location/building
    IF NOT (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))        IS NULL 
      SET @START_LOCATION = (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE
      SET @START_LOCATION = ''; 
    IF NOT (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_RAUM_NR FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_ROOM_NO FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_FLAECHE_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_AREA_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
-- Rack and stuff
    IF NOT (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_RACK = '#' + (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE 
      SET @START_RACK = '';
      IF NOT (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
        SET @START_RACK = @START_RACK + '.' + (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_RACK = @START_RACK + '.b' +                                                          (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

----------------------------------------------------------
-- END component
----------------------------------------------------------
[...] similar statements as above, omitted for better reading

-- Final assembly of label tag string and DB update 
SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' + @END_KOMP_ID + @END_LOCATION + @END_RACK;
-- Insert statements for procedure here
IF @Buffer <> ''
  BEGIN                                                                                             UPDATE CABLES_LIST SET CONCAT_VALUE = @Buffer WHERE KeyField=(SELECT KeyField FROM inserted);                                                                                   END
END                                                                                             ------------------------------------------------------------------------------------------------- Multi Row Part                                                                             ------------------------------------------------------------------------------------------------
ELSE
  BEGIN
    OPEN tableCursor
    FETCH NEXT FROM tableCursor INTO @KeyField
    WHILE @@FETCH_STATUS = 0
      BEGIN
      -- ID part for component/technical place
        IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField = @KeyField) IS NULL
      -- component ID is not according to nomenclature
          IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField = @KeyField) = 1
            SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField =                  @KeyField) + '>';
          ELSE
            SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KeyField =                        @KeyField);
        ELSE
          SET @START_COMP_ID = '';
        IF NOT (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField) IS NULL
          SET @FUNCTION = '[' + (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField);
        ELSE
                                                                                            [...] the structure of the omitted code follows the code for the 1 row update. Statements of the type KeyField=(SELECT KeyField FROM inserted) are always replaced by KeyField = @KeyField   
SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' +
  @END_COMP_ID + @END_LOCATION + @END_RACK;
-- Update table and loop to next row of INSERTED table
IF @Buffer <> ''
Begin
UPDATE CABLES_LIST SET ID_ACCNOMEN = @Buffer, QR_CODE_TEXT = 'CAB:' + @Buffer WHERE KABEL_ID = @CableID;
END
FETCH NEXT FROM tableCursor INTO @CableID
END
END
END
Eingestellt von um
Labels: , ,