SQL Server Performance

Problem with updating tables in a trigger

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by eni, Nov 12, 2007.

  1. eni New Member

    Hello,I have a trigger-problem with the following code. The code below works fine, but I would like to automate it as follows:When the STATUS in TEST_TABLE changes, TRIGGER_NAME and VALUE are fetched from TRIGGER_TABLE according to the COMP AND STATUS fields of the (updated) Inserted-table. The field name that is the same than the fetched TRIGGER_NAME is updated in TEST_TABLE and gets a new value (namely VALUE). The problem is that basicly there can be many different records in TRIGGER_TABLE AND inserting/updating records in TRIGGER_TABLE needs the trigger to be updated (new IF-clauses according to FIELDx..).I would like to do the UPDATE-clauses like this:UPDATE TEST_TABLE SET @trigger_name = @value, @trigger_name + '_DTE' = GETDATE() WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)END.. but SQL-queries, where is no exact field name after SET, are not allowed. Can I use other techniques, can system tables help in this case?Regards,-Esa---- code starts here ----CREATE TRIGGER TESTTRIGGER ON TEST_TABLEFOR UPDATEASIF NOT UPDATE(STATUS)BEGINRETURNENDELSEBEGINDECLARE @trigger_name varchar(20)DECLARE @value varchar(10)SELECT @trigger_name = (SELECT TRIGGER_NAME FROM TRIGGER_TABLE WHERE COMP IN (SELECT COMP FROM Inserted) AND LAUNCH_STATUS IN (SELECT STATUS FROM Inserted))SELECT @value = (SELECT VALUE FROM TRIGGER_TABLE WHERE COMP IN (SELECT COMP FROM Inserted) AND LAUNCH_STATUS IN (SELECT STATUS FROM Inserted))IF (@trigger_name = '') OR (@value = '')BEGIN RETURNENDELSEIF @trigger_name = 'FIELD1'BEGINUPDATE TEST_TABLE SET FIELD1 = @value, FIELD1_DTE = GETDATE() WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)ENDELSEIF @trigger_name = 'FIELD2'BEGINUPDATE TEST_TABLE SET FIELD2 = @value, FIELD2_DTE = GETDATE() WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)ENDEND
  2. Adriaan New Member

    You either need to spell out the queries for each possible column that you need to cover, or you have to use dynamic SQL, where you concatenate the column name with the skeleton query statement, and then execute the whole string.
    Problems you will run into is that the UPDATE() check only accepts a constant expression for the column name, and doesn't accept variables - but that can be bypassed with dynamic SQL as well..
  3. eni New Member

    Thanks for your answer!I studied some dynamic SQL (new to me) and, if I understood right, you meant concatenating the SQL-clause as follows. At least now it works as it is supposed to :)----DECLARE @SQL VarChar(1000)SELECT @SQL = 'UPDATE TEST_TABLE SET 'SELECT @SQL = @SQL + @trigger_nameSELECT @SQL = @SQL + ' = '''SELECT @SQL = @SQL + @valueSELECT @SQL = @SQL + ''' WHERE FIELD_ID IN ('''SELECT @SQL = @SQL + @fieldSELECT @SQL = @SQL + ''') AND COMP IN ('''SELECT @SQL = @SQL + @compSELECT @SQL = @SQL + ''')'EXEC(@SQL)----At the first sight, it doesn't look very nice code, but anyway, it's not so difficult to use.@field and @comp are built same way than @trigger_name and @value but from different table (TEST_TABLE)..Regards,-EsaPS. I don't know why at least in my previous mail, the whole text was shown in a one long sentence!?

Share This Page