'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:
Is the table
deletedcreated before the trigger is executed, because I do not understand how we have tabledeletedeven though we have not deleted anything from the table(we useinstead of delete, notafter deletetrigger)Also, what does exactly
begindo? I read that it is used to group some statements of code. Would it be correct if I skipbegin?In the book is written that here it would not be correct to define
after delete trigger, becauseproducerc#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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

