CODEWITHSUNDEEP
sqlsql-servertriggerscursor
user3885166
user3885166

Reputation: 215

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

Upvotes: 0

Views: 106

Answers (2)

Michael Petito
Michael Petito

Reputation: 13161

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.

Upvotes: 1

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

The deleted contains old values, the inserted - new values. There are options: AFTER UPDATE or BEFORE UPDATE which you can set to see behavior which you want.

About Deleted and Inserted: UPDATE dbo.Client SET Name = 'John' WHERE Name = 'Steve' you will have: deleted value 'Steve', inserted value 'John'

Also I would recommend to avoid using cursors inside triggers, i.e. it can hit performance a lot.

Upvotes: 0

Related Questions