CODEWITHSUNDEEP
sqlsql-serverdatabasetriggerssql-update
hattenn
hattenn

Reputation: 4399

SQL Server update trigger is not working

There's a database table that has an update trigger. Whenever a column is updated, one of its columns is automatically calculated.

I have tweaked the trigger and I'd like to run it on all of the rows again. In SQL Server Management Studio, if I choose "Edit Top 200 Rows" on my table and edit one of the rows, the update trigger works. But when I write a query like:

UPDATE MyTable
SET SomeIrrelevantColumn = 0

the trigger doesn't work, the column that is supposed to be calculated by the trigger stays the same.

How can I run the trigger manually on all the rows?

Edit: Here's the trigger:

USE [MY_DATABASE]
GO
/****** Object:  Trigger [dbo].[MY_TABLE_AUER]    Script Date: 04/24/2013 00:05:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MY_TABLE_AUER] 
   ON  [dbo].[MY_TABLE] 
   AFTER UPDATE
AS 
DECLARE @UPD_COLUMN_A int,
        @INS_COLUMN_A int,
        @UPD_COLUMN_B int,
        @UPD_COLUMN_C varchar(255),
        @UPD_COLUMN_D varchar(255),
        @UPD_COLUMN_E int,
        @UPD_COLUMN_F datetime
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT @UPD_COLUMN_A = _Column_A,
           @UPD_COLUMN_B =_Column_B,
           @UPD_COLUMN_C = COLUMN_C,
           @UPD_COLUMN_D = [Column_D]
      FROM DELETED;
    IF @UPD_COLUMN_D not like '%SomeString%'
    BEGIN
      SELECT @INS_COLUMN_A = _Column_A
        FROM INSERTED;
      IF @UPD_COLUMN_A != @INS_COLUMN_A
      BEGIN
        SELECT @UPD_COLUMN_E = MAX([_Column_B]),
               @UPD_COLUMN_F = MAX([_Column_G])
          FROM MY_TABLE
         WHERE COLUMN_C = @UPD_COLUMN_C
           AND [Column_D] LIKE '%SomeString%';
        UPDATE MY_TABLE 
           SET COLUMN_E = @UPD_COLUMN_E,
               COLUMN_F = @UPD_COLUMN_F
         WHERE [_Column_B] = @UPD_COLUMN_B;
        UPDATE MY_TABLE 
           SET COLUMN_H = @UPD_COLUMN_B
         WHERE [_Column_B] = @UPD_COLUMN_E; 
      END
    END
END

Upvotes: 1

Views: 25595

Answers (3)

Stalion
Stalion

Reputation: 1

Please try using the below code..in trigger..!

//Check if the column is getting updated

if (Update(column1) or Update(Column2)...etc)

Begin

  Write the entire logic inside this loop..!

End

Hope this works..!!!

Upvotes: -2

ErikE
ErikE

Reputation: 50241

Your trigger makes a very common but false assumption that it will execute once per row. It doesn't, it executes once per action--so when you update the entire table, I bet if you look closer, you'll see that one row was updated. * with thanks to Aaron Bertrand's comment for this intro paragraph.

You'll need to look into how to perform an update based on a JOIN (with the inserted or deleted meta-tables). For example:

CREATE TRIGGER TR_Sample_U ON dbo.Sample FOR UPDATE -- AFTER is default so not needed
AS
IF EXISTS ( --check for disallowed modifications
   SELECT *
   FROM
      Inserted I
      INNER JOIN Deleted D
         ON I.SampleID = D.SampleID
   WHERE
      I.Something <> D.Something
      AND I.UpdateDate = D.UpdateDate
)
ROLLBACK TRAN;

These resources may also help you:

Upvotes: 8

hattenn
hattenn

Reputation: 4399

I have found out that one way to do it is something like the following:

UPDATE A
SET A.SOME_COLUMN = D.ANOTHER_COLUMN
FROM MY_TABLE AS A
JOIN inserted AS B ON A.ID = B.ID -- Point 1.
JOIN deleted AS C ON A.ID = C.ID
JOIN (SELECT MAX(ID) AS OTHER_ID, GROUP_ID AS OTHER_GROUP_ID -- Point 2.
      FROM MY_TABLE AS E
      WHERE E.SOME_STRING_COLUMN LIKE '%SomeString%'
      GROUP BY E.GROUP_ID) AS D ON A.GROUP_ID = D.OTHER_GROUP_ID
WHERE C.SOME_BOOL_COLUMN != B.SOME_BOOL_COLUMN -- Point 3.
      AND C.SOME_STRING_COLUMN NOT LIKE '%SomeString%'
  1. After the basic UPDATE statement, I go ahead and join the table to 'inserted' and 'deleted' special tables (more info about those at http://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/). This way, I'll only go through the rows that have been updated, and I won't mess with the other ones. You can only join with one of them, but I needed to see a difference in one column between the values before and after the update operation. So that's why I used them both. 'deleted' has the row in the state before the update operation, and 'inserted' has the row in the state after the update operation.
  2. After that, I need to find a row in the whole table that is calculated by a value in the row that is currently being updated. In my case, there was a parent of the current row and I wanted to find that row. Those two rows shared the same GROUP_ID, and I made a string test to make sure that the new row I get is qualified as a parent, you can define any kind of filter there. Basically, you write another query to find the row based on your updated row, and then you make another JOIN to that returned table.
  3. And last, I used a WHERE clause to make sure that I only update the rows that has changed state by looking at the SOME_BOOL_COLUMN column. You can put any kind of criterion here. As you can see I check the difference between the state of the column before and after the update.

Take whatever written here with a grain of salt though, as it's coming from someone that has virtually no experience with SQL.

Upvotes: 0

Related Questions