SQL - Delete updated row in update trigger

Question

I have a problem with my update trigger

ALTER TRIGGER tr_MyTable
ON  MyTable
AFTER UPDATE
AS 

BEGIN TRAN
SET NOCOUNT ON;

      If Update(column)
      Begin
           DELETE FROM MyTable WHERE <condition>
      End


      SET NOCOUNT OFF
COMMIT TRAN
GO

I'm trying to delete a row (from MayTable) that currently has been updatete in MyTable. But the row is not deleted when I run the trigger. Is it not possible to delete a row that currently has been updated inside the update trigger? (The delete and update is on the same table)

Thanks!

EDIT:

ALTER TRIGGER tr_MyTable
ON  MyTable
AFTER UPDATE
AS 

BEGIN TRAN
SET NOCOUNT ON;

      SELECT @ID = ID
      FROM   inserted

      If Update(column)
      Begin
           DELETE FROM MyTable WHERE ID = @ID
      End


      SET NOCOUNT OFF
COMMIT TRAN
GO

EDIT 2:

ALTER PROCEDURE sp_Update
(
@ID INTEGER,)
AS

SET NOCOUNT ON
Begin Tran

            Update MyTable SET Column = Data where ID = @ID

      Commit Tran
SET NOCOUNT OFF
GO

This update prosedure is working and the column is updated. The trigger is also fired and the line beford the delete (print statement) is executed but not the delete statement. But when I update the column directly in the table, not form the prosedure the trigger fires and the delete statment is working like it should.I use no rollback.

Solution 1

how are you deleting.?

you should be reading your values on from your INSERTED and DELETED tables, like:

DELETE FROM MyTable WHERE id in (select ID from INSERTED)

Solution 2

Maybe you want to do this in an INSTEAD OF trigger. If someone tries to update that column, you can delete the row, otherwise you re-perform the update. The only challenge here is that you have to re-code the update statement within the trigger. (Also you don't really need an additional BEGIN TRAN/COMMIT TRAN inside the trigger.)

CREATE TRIGGER dbo.tr2_myTable
ON dbo.MyTable
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  -- update the rows where the column hasn't changed
  UPDATE s
    SET col1 = i.col1, col2 = i.col2, ...
    FROM dbo.MyTable AS s
    INNER JOIN inserted AS i
    ON s.key = i.key
    INNER JOIN deleted AS d
    ON i.key = d.key
    AND i.column_that_should_not_change = d.column_that_should_not_change;

  -- delete the rows where the column HAS changed
  -- (note that this requirement sounds odd to me)
  DELETE s
    FROM dbo.MyTable AS s
    INNER JOIN inserted AS i
    ON s.key = i.key
    INNER JOIN deleted AS d
    ON i.key = d.key
    AND i.column_that_should_not_change <> d.column_that_should_not_change;
END
GO

Note that unlike your IF UPDATE() logic, this trigger will actually handle multiple rows (e.g. some where that column has changed, and some where it hasn't). Note also that IF UPDATE(x) will be true even if someone said UPDATE foo SET x = x;.

This assumes you have a key column and that column_that_should_not_change is not nullable. If this column allows NULLs then the logic gets a little more complex.

Solution 3

It is possible.

If you want, you could have DELETE FROM MyTable in your trigger and that should work. Have you tested to confirm that trigger is being executed and your code reaches the DELETE FROM MyTable WHERE <condition> line?

Comments

[Liss]

yes, the trigger is beeing executed and the line before the delete from MyTable where <condition> line is executed so the delete line should also be executed. I select my <condition> from the inserted table as Diego mentioned.

[daniloquio]

Then I think it has to be that <condition> is always false. Why don't you show us the full DELETE line code?

[daniloquio]

What is "If Update(column)" doing? Are you 100% sure Id is unique? ... for what I see, it should be working. I created a similar escenario in my machine and there the updated row is deleted succesfuly.

[Liss]

Maby this is my problem... my case is that I update a column in MyTable from a stored prosedure, and when I do that the trigger fires, and in the trigger I do the delete. But is the problem that I do the update from the stored prosedyre and the trigger is fired befor the whole operation is commited (before the prosedure with the update statement is commited)?

[daniloquio]

A stored procedure is like a big transaction, and the trigger would be a nested transaction. It should work also from a stored procedure. Except that... is any "rollback" instruction in the stored procedure? Could you show your full code including the full procedure and the full trigger?

[Liss]

It is a lot of code but I have updated my original post with a simple version....the prosedure is working like it should. But see my comment about updateing directly in the table. Thanks for all your help!!

[daniloquio]

let us continue this discussion in chat