Search code examples
sqlsql-servertriggerscursor

Creating a trigger instead of UPDATE on a view

I have a view and a trigger that overwrites UPDATE. I try to understand the code, however I am having some troubles with line from deleted and from inserted - Are these automatically created by original update? Does it mean that if I call this trigger, first the update gets called and then the trigger?

create trigger updateView on View
instead of UPDATE as
begin
    declare @nameK VARCHAR(100), @addresK VARCHAR(100), @nameZ VARCHAR(100), @number INT, @date DATE;
    declare @nameKo VARCHAR(100), @addresKo VARCHAR(100), @nameZo VARCHAR(100), @numbero INT, @dateo DATE; -- 'o'ld

    declare insCur cursor for select name, addres, bandName, number, date from inserted;
    declare delCur cursor for select name, addres, bandName, number, date  from deleted;
    open insCur;
    open delCur;
    ...
Solution

  • This trigger will fire instead of performing the update. The inserted and deleted tables are made available so that you can determine:

    1. The new values for each updated row in inserted
    2. The previous (in this case current, since the normal update operation has not been performed) values from each updated row in deleted

    The convention of inserted and deleted is used regardless of the type of trigger. So, for example, in an after update trigger, which simply follows the execution of a normal update, the deleted table would be the primary way to know the previous values.