Update Trigger: w/ cursor, w/o cursor

use lab9;
go

--==============================================================
-------------------------UPDATE-ON-VIEW-------------------------
--==============================================================
if OBJECT_ID(N'dbo.joined_trigger_update', N'TR') is not null
    drop trigger dbo.joined_trigger_update
go
create trigger dbo.joined_trigger_update
    on dbo.joined_view
    instead of update
    as
    begin
        set nocount on;
        declare @entry varchar(35)
        declare @new_param varchar(35)

        if UPDATE(title) RAISERROR('[TRIGGER UPD]: "title" cant be modified', 16, 1)
        if UPDATE(author) RAISERROR('[TRIGGER UPD]: "author" cant be modified', 16, 1)

        if UPDATE(year)
        begin
            create table temp (
                title       varchar(254),
                year_new    int)

            insert into temp select i.title, i.year from inserted as i

            declare curs cursor
                for select temp.title, temp.year_new
                from temp

            open curs
            fetch next from curs into @entry, @new_param

            while (@@FETCH_STATUS = 0)
            begin
                update dbo.books
                    set books.year = @new_param
                    where books.title = @entry

                fetch next from curs into @entry, @new_param
            end

            close curs
            deallocate curs
            drop table temp
        end

        if UPDATE(rating)
        begin
            create table temp (
                title       varchar(254),
                rating_new  int)

            insert into temp select i.title, i.rating from inserted as i

            declare curs cursor
                for select temp.title, temp.rating_new from temp

            open curs
            fetch next from curs into @entry, @new_param

            while (@@FETCH_STATUS = 0)
            begin
                update dbo.books
                    set books.rating = @new_param
                    where books.title = @entry

                fetch next from curs into @entry, @new_param
            end

            close curs
            deallocate curs
            drop table temp
        end
    end
go

--==============================================================
-------------------------UPDATE-ON-VIEW-------------------------
--==============================================================
if OBJECT_ID(N'dbo.joined_trigger_update', N'TR') is not null
    drop trigger dbo.joined_trigger_update
go
create trigger dbo.joined_trigger_update
    on dbo.joined_view
    instead of update
    as
    begin

        if UPDATE(title) or UPDATE(author)
            RAISERROR('[UPD TRIGGER]: "title" and "author" cant be modified', 16, 1)

        if UPDATE(year) or UPDATE(rating)
            update dbo.books
                set
                    books.year = (select year from inserted where inserted.title = books.title),
                    books.rating = (select rating from inserted where inserted.title = books.title)
                where books.title = (select title from inserted where inserted.title = books.title)

    end
go



update dbo.joined_view
    set rating = 451,
        year = 451
    where joined_view.author = 'raymond bradbury';
update dbo.joined_view
    set rating = 10
    where joined_view.author = 'george martin';
update dbo.joined_view
    set year = 2045
    where joined_view.title = 'a dream of spring';
select * from dbo.joined_view
select * from dbo.books