none
(SQL Server 2005) Instead of Delete Trigger

    Question

  • Hi,

    I simply need a trigger to delete some datasets from a view but have some trouble writing an adequate trigger. Here is my attempt:

    Use myDB;
    Go
    CREATE TRIGGER IO_Trig_Del_myView ON myView
    INSTEAD OF Delete
    AS
    BEGIN
    SET
    NOCOUNT ON
    -- Check for dataset, if present delete.
    IF (EXISTS (SELECT Z.[myPk]
    FROM myTable t, deleted
    WHERE t.[myPk] = deleted.[myPk]))
    Delete From myTable
    Where myTable.[myPk] = deleted.[myPk]...

    This causes the following failure:

    Msg 4104, Level 16, State 1, Procedure IO_Trig_Del_myView, Line 11
    The multi-part identifier "deleted.myPK" could not be bound.

    Can somebody explain the reason to me? myPk is part of the View I created. Since I do have three tables in myView so I get that message three times, once per table.

     
    Wednesday, May 31, 2006 12:33 PM

Answers

  • Hi...

     

    The deleted object is a Table and you have to treat it as such. That means it can contain more then one row of data so you have to take this into account in your querry...

     

    You could try the following querry

    Delete From myTable
    Where myTable.[myPk] in ( select [myPk] from deleted) ...

    Wednesday, May 31, 2006 12:48 PM

All replies

  • Hi...

     

    The deleted object is a Table and you have to treat it as such. That means it can contain more then one row of data so you have to take this into account in your querry...

     

    You could try the following querry

    Delete From myTable
    Where myTable.[myPk] in ( select [myPk] from deleted) ...

    Wednesday, May 31, 2006 12:48 PM
  • Thanks.

    Although I don't understand it. Isn't it possible to select more than one row via the = operator? I mean if I take a join for example I compare via = and can get more than one row and also compare more rows.

    Wednesday, May 31, 2006 1:05 PM
  • The = can only be used to compare one Value against another one. To compare one Value against a set of other values you need the "in" clause. The syntax of a join is a little different here. In fact it could also be rewritten as a join to achieve the same result by my preference to doing delete statements is to first write the querry that will show me what to delete and then encapsulate that querry inside the In clause (Thats just a personal preference)

    Wednesday, May 31, 2006 1:12 PM
  • Check out this delete trigger.  This will do what you want, but you have to include the deleted table in the trigger (I am using a correlated subquery, but a join would work to)

    create TABLE test
    (
        myPk int PRIMARY key
    )
    go
    CREATE VIEW myView
    AS
        SELECT myPk
        FROM   test
    go

    CREATE TRIGGER IO_Trig_Del_myView ON myView
    INSTEAD OF Delete
    AS
    BEGIN
    SET NOCOUNT ON

    -- Check for dataset, if present delete. (do this in one step
    DELETE FROM myTable
    WHERE EXISTS (SELECT *
                                     FROM deleted
                                     WHERE myTable.myPk = deleted.myPk)
    END
    go

    --Code to test with...

    INSERT INTO test
    VALUES (1)
    INSERT INTO test
    VALUES (2)
    INSERT INTO test
    VALUES (3)
    INSERT INTO test
    VALUES (4)
    INSERT INTO test
    VALUES (5)

    SELECT * FROM test
    DELETE FROM myView WHERE myPk = 1
    SELECT * FROM test
    DELETE FROM myView WHERE myPk IN (2,4)
    SELECT * FROM test

    Thursday, June 01, 2006 1:16 AM
    Moderator