'Difference between instead of and after trigger in SQL?

I am new to databases and I have trouble understanding triggers. Any help would be appreciated. Thank you in advance!

My task is to create a trigger that sets producerc# = null in the table movie, when cert# from table movieexec is deleted.

I have two solutions for this task.

create trigger t
on movieexec
instead of delete
as 
    begin 
        update movie
        set producerc# = null
        where producerc# in (select cert# from deleted);

        delete from movieexec
        where cert# in (select cert# from deleted);
    end;
go

This solution is not mine, it is from a book.

I have some questions about it:

  1. Is the table deleted created before the trigger is executed, because I do not understand how we have table deleted even though we have not deleted anything from the table(we use instead of delete, not after delete trigger)

  2. Also, what does exactly begin do? I read that it is used to group some statements of code. Would it be correct if I skip begin?

  3. In the book is written that here it would not be correct to define after delete trigger, because producerc# is a foreign key. Can you explain to me, is that true and why is that.

When I test my code, both of the solutions work fine.

This is my solution:

create trigger t
on movieexec
after delete
as
    update movie
    set producerc# = null
    where producerc# not in (select cert# from movieexec);

Do these solutions do the same thing, are both of them correct?

Can you give me some example where any of them does not work as expected.

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source