Post reply

Newbie question - Views & delete

  • January 16, 2004 at 8:49 am

    #84353

    I got a request from a superuser to delete certain records within a db. When I was unable to locate the table she claimed they were in I found that it a view instead. I am now leary about her request since I am not sure if SQL 2000 will allow deletions from a view, and what happens to all the corresponding records in the tables that comprise the view. Is this at all possible? What are the pitfalls? What should I watch out for, or should I just deny the request?

  • January 16, 2004 at 9:20 am

    #490458

    Views based on a single table are no problem.

    I've not ever attempted to even try to delete from view where the view was comprised of 2 or more tables.

    Be intrested to see what others post.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 9:43 am

    #490472

    I found that the view is comprised of three tables. Tables 1 & 2 are (inner) joined on an id field and Tables 2 7 3 are inner joined on a secondary id field. If I delete records using an SQL stement like DELETE * FROM myview WHERE date = '1/16/2004' ... What would happen? Will it delete the records from all 3 tables? or do I have to do this manually to each table?

  • January 16, 2004 at 12:56 pm

    #490528

    You can not delete from a view that references multiple tables (AFAIK)

    Try this:

    use tempdb

    create table one (nbr int)

    create table two (nbr int)

    create table three (nbr int)

    insert into one select 1 union all select 4 union all select 5

    insert into two select 1 union all select 4 union all select 7

    insert into one select 1 union all select 4 union all select 9

    create view myView as

    --you can not do this as the colums have to be unique

    --select * from one join two on one.nbr=two.nbr join three on one.nbr = three.nbr

    select one.nbr as nbr1, two.nbr as nbr2,three.nbr as nbr3

    from one join two on one.nbr=two.nbr join three on one.nbr = three.nbr

    delete from myview

    you get this error:

    Server: Msg 4405, Level 16, State 1, Line 1

    View or function 'myview' is not updatable because the modification affects multiple base tables.

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • January 16, 2004 at 1:06 pm

    #490529

    Thanx! You just confirmed my suspicions. You guys are all great! I am learning a lot just reading you posts. Hopefully one day I will be able to contribute more. Thanx again.

  • January 16, 2004 at 1:11 pm

    #490534

    Good questions can contribute just as much, or even more than good answers, and always are better than bad answers.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 1:45 pm

    #490538

    I completely agree Thomas, good questions have led me to test some cool scenarios and thus learn more! (isn't that everyone's goal?)

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • February 3, 2004 at 11:58 am

    #492667

    You can delete from a view by using an INSTEAD OF trigger on the view.  Using Ray's example:

    CREATE TRIGGER d_myView ON myView

    INSTEAD OF DELETE AS

    DELETE One

    FROM One JOIN deleted ON Nbr = Nbr1

    DELETE Two

    FROM Two JPIN deleted ON Nbr = Nbr2

    DELETE Three

    FROM Three JOIN deleted ON Nbr = Nbr3



    --Jonathan

  • February 3, 2004 at 12:27 pm

    #492669

    Jonathan



    Once you understand the BITs, all the pieces come together

  • February 3, 2004 at 5:31 pm

    #492691

    Yep, placing an instead of trigger will work, I forgot about that, haven't ever used it.

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply