SQL Server trigger Insert/Update specific column

Question

Point is to make a trigger which will:

Check the configuration table which contains a column ConnectionField nvarchar(50)
It should return the string value (columnName) which will be used as a key

So on insert/update on table Workers, the code should set my Xfield value to the value from column ConnectionField, read from the Configuration table.

In short since this is all messy. I want to be able to let my end user to write down in configuration which column he will use as unique (Worker ID, SNSID, Name etc... ) based on his pick trigger need to put that field value to my Xfield

Don't ask why. It's really confusing.

I've written a trigger which will do that but it just is stuck somewhere in an infinite loop

CREATE TRIGGER [dbo].Tr_ConnectionField 
ON [dbo].Workers
FOR INSERT, UPDATE 
AS
    SET NOCOUNT ON;

    DECLARE @ID BIGINT
    DECLARE @tmpUpit CURSOR;
    DECLARE @ConFieldSETUP NVARCHAR(50)

    -- Here I will read the field from configuration which will be used as key
    SET @ConFieldSETUP = (SELECT TOP 1 ISNULL(ConnectionField, 'SNSID') 
                          FROM ConfigurationTable)

    BEGIN
        SET @tmpUpit = CURSOR LOCAL SCROLL FOR
            SELECT i.id FROM inserted i

        OPEN @tmpUpit
    END

    FETCH NEXT FROM @tmpUpit INTO @ID 

    WHILE @@fetch_status = 0
    BEGIN
        -- Here I will use the configuration columns value to my Xfield
        UPDATE Workers 
        SET Xfield = (SELECT @ConFieldSETUP 
                      FROM Workers cld 
                      WHERE cld.Id = @ID) 
        WHERE Id = @ID
    END

    FETCH NEXT FROM @tmpUpit INTO @ID

    DEALLOCATE @tmpUpit

First, your trigger sets `Xfield` to the value of variable `@ConFieldSETUP`, 'SNSID' for example. To use `@ConFieldSETUP` as a name of the column you need dynamic sql. Or better a `case` expression to select one of the source column depending on `@ConFieldSETUP`. Next, use join with `inserted` instead of cursor. Finally, prevent trigger recursion with one of the methods https://stackoverflow.com/questions/1529412/how-do-i-prevent-a-database-trigger-from-recursing — Serg, Jul 21 '19 at 16:34
Your second FETCH NEXT needs to be in the loop, not after it. — David Browne - Microsoft, Jul 21 '19 at 20:19
@Serg well you are willing to say i need some kind of string variable which will concat the string as query and then execute it or ?. I don't know what you mean under dynamic query. — Programer Anel, Jul 22 '19 at 06:14
Yes, concat the string as query and then execute it is known as dynamic sql. But using it in triggers is questionable, a dynamic sql has no access to `inserted` `deleted` tables. If you still need iI would advice a stored procedure with a table parameter of `Id`s from `inserted`. — Serg, Jul 22 '19 at 06:33

score 1 · Accepted Answer · answered Jul 22 '19 at 06:26

1

Try

CREATE TRIGGER [dbo].Tr_ConnectionField ON [dbo].Textt
FOR INSERT, UPDATE AS

    SET NOCOUNT ON;
    DECLARE @ConFieldSETUP nvarchar(50);
    -- Stop recursion for the trigger
    IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.Tr_ConnectionField')) > 1
        RETURN;
    -- Here i will read the field from configuration which will be used as key
    SET @ConFieldSETUP = (SELECT TOP 1 ISNULL(ConnectionField, 'SNSID') 
                          FROM ConfigurationTable
                          -- ORDER BY ...          
                          );
    -- Update Xfield depending on configuration
    UPDATE w
        SET Xfield = CASE @ConFieldSETUP 
                         WHEN 'SNSID' THEN w.SNSID
                         WHEN 'Name'  THEN w.Name
                         ...
                     END
    FROM Workers w
    JOIN inserted i ON i.Id = w.Id;

answered Jul 22 '19 at 06:26

Serg

22,285
5
21
48

Before i mark this as a solution ? Is this going to work on bulk insert ? I.E `Insert into Workers Select .... From AnotherTable` ?? I used cursor only because of that reason – Programer Anel Jul 22 '19 at 06:34
@ProgramerAnel, You should add `FIRE_TRIGGERS` parameter to `BULK INSERT` command https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017 – Serg Jul 22 '19 at 06:38
`Insert .. select from AnotherTable` is a regular insert, provided AnotherTable is not `OPENROWSET(BULK...) ` See list of BULK operations at https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-server-2017 – Serg Jul 22 '19 at 06:53
@ProgramerAnel A [`case`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017#return-types) expression returns a single data type: "Returns the highest precedence type from the set of types in _result_expressions_ and the _optional else_result_expression_." What is the data type of `Xfield` that will accommodate [The Answer](https://en.wikipedia.org/wiki/Phrases_from_The_Hitchhiker's_Guide_to_the_Galaxy#Answer_to_the_Ultimate_Question_of_Life,_the_Universe,_and_Everything_(42))? – HABO Jul 22 '19 at 14:10

SQL Server trigger Insert/Update specific column

1 Answers1