CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
ali.qb
ali.qb

Reputation: 45

SQL Server trigger: Delete From Table AFTER DELETE

I have a parent table called announcement like this

create table announcement 
(
    aid int primary key, 
    name varchar,
    ......
)

and another table cars that inherits from announcement (tpt) like this

create table cars
(
     aid int primary key 
             foreign key references announcement(aid) on delete cascade,
     .....
)

I know that if I delete a row from announcement, that record will be deleted from cars too because of the "on delete cascade".

But I want to became sure that if any deletion from cars has been happened, that row would also be deleted from announcement.

I wrote a trigger than I learn from this question like this:

create trigger delCarFromA 
on car
after delete 
as
begin 
    delete from announcement 
    where announc.aid = car.aid;
end;

but I get this error:

The multi-part identifier "car.aID" could not be bound.

What should I do?

Upvotes: 3

Views: 11523

Answers (2)

Earnest
Earnest

Reputation: 151

Try the below code:

CREATE TRIGGER delCarFromA on cars
    FOR DELETE
AS
    DELETE FROM dbo.announc
    WHERE aid IN(SELECT deleted.aid FROM deleted)
GO

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Your tables are 1:n related. An announcement can have many cars, a car can have one announcement. If you delete an announcement you delete all cars cascadingly. But the other direction would need a rule, e.g.

  1. If one car gets deleted, its announcement with all its other cars must be deleted.

or

  1. If an announcement's last car gets deleted (i.e. the announcement has no cars) then the announcement must be deleted.

I assume you want the second rule. Ideally, your trigger would be an after-statement trigger, but SQL Server does not support this. Unlike defined in the SQL standard, every trigger in SQL Server is a FOR EACH ROW trigger implicitly.

In a FOR EACH ROW trigger you can access the deleted car row. But this is not done via car.aid, but with (select aid from deleted) in SQL Server.

If you want the second rule applied, as I assume, you also need an EXISTS clause to see whether the announcement still has cars.

create trigger delCarFromA on car
after delete
as
begin 
  delete from announcement a
  where aid = (select aid from deleted)
  and not exists
  (
    select null
    from car c
    where c.aid = a.aid
  );
end;

If you want to delete the announcemmnt even when it still has cars, then remove the EXISTS clause from the trigger.

Upvotes: 5

Related Questions