(SQL Server 2005) Instead of Delete Trigger

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.

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) ...

|||

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.

|||

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)

|||

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