Deleting a record from a database

Discussion in 'ASP .Net' started by bthumber, Jul 20, 2009.

  1. bthumber

    bthumber Guest

    I need to delete a record form a database, but I don't want to lose that
    record. I was told I could set a flag, so the question is how do you do that?
    bthumber, Jul 20, 2009
    #1
    1. Advertising

  2. bthumber

    sloan Guest

    Some people call this "soft deleting".

    Create table dbo.Employee
    {
    EmployeeUUID uniqueidentifier primary key not null default newsequentialid()
    ,
    SSN varchar(11) not null ,
    IsDeleted bit default (0)
    }



    Then your "soft delete" command would be

    Update dbo.Employee Set IsDeleted = 1 where SSN = '222-22-2222'

    Something like that.

    Of course when you want to get all employees...........you filter

    Select EmployeeUUID, SSN from dbo.Employee e where e.IsDeleted = 0




    "bthumber" <> wrote in message
    news:...
    >I need to delete a record form a database, but I don't want to lose that
    > record. I was told I could set a flag, so the question is how do you do
    > that?
    sloan, Jul 20, 2009
    #2
    1. Advertising

  3. =?Utf-8?B?YnRodW1iZXI=?= <> wrote in
    news::

    > I need to delete a record form a database, but I don't want to lose
    > that record. I was told I could set a flag, so the question is how do
    > you do that?
    >


    There are two ways to set up a logical delete.

    1. Add a field in the table called IsDeleted. This will be a bit type (SQL
    Server) or a Boolean type (Access - hopefully you are not using Access).
    The addition is:

    ALTER TABLE Table1
    ADD
    IsDeleted bit default 0 NOT NULL
    GO

    You then have to alter your SQL so it respects this:

    AND IsDeleted = 0

    Except on the page where you can "undelete" an item, which would look for
    deleted items only.

    AND IsDeleted = 1

    To delete, the command is like this:

    UPDATE Table1
    SET IsDeleted = 1
    WHERE ID = @id

    2. Create a trigger that archives the "deleted file" To do this, you create
    a table exactly like the original table, but you call it _History.

    CREATE TABLE Table1_History
    (
    -- Fields go here
    )
    GO

    You can then set up a trigger:

    CREATE TRIGGER trgTable1Delete ON Table1
    FOR DELETE
    AS

    INSERT INTO Table1_History
    (
    -- Field names here
    )
    SELECT *
    FROM deleted

    This will completely remove the record, but will make a copy in a
    "history" table, so it is a better option if you have already heavily
    invested in queries against this table.

    You can restore by reversing the delete (ie, deleting from history and
    inserting into the original table).

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 20, 2009
    #3
  4. bthumber

    sloan Guest

    I should have followed up, but the "History" idea is alot cleaner than the
    SoftDelete flag/column.

    I call this "Archive" (just another term for it) and my table(s) would
    look like this:


    Create table dbo.Employee
    {
    EmployeeUUID uniqueidentifier primary key not null default newsequentialid()
    ,
    SSN varchar(11) not null
    }


    Create table dbo.EmployeeArchive
    {
    EmployeeArchiveUUID uniqueidentifier primary key not null default
    newsequentialid()
    ,
    SSN varchar(11) not null
    }


    The one area you have to be careful.
    If you (correctly) have put a UNIQUE CONSTRAINT on SSN (for example)....you
    might get into the situation where you
    1. Have a row. (Lets say SSN of '222-22-2222')
    2. Delete (and "archive") the row. (So dbo.EmployeeArchive has a row with
    '222-22-2222')
    3. The row is re-added to the primary table. (Lets say an employee is
    rehired). '222-22-2222' exists in dbo.Employee AND dbo.EmployeeArchive)
    4. You try to delete again, and if you have the UNIQUE constraint on the
    Archive table (on SSN)...........it will fail. (because you're trying to jam
    2 rows of '222-22-2222' into dbo.EmployeeArchive)

    So the thing you gotta remember...when you "Add New", you have to check and
    see if you're able to resurrect an Archive(d) row!




    CREATE TRIGGER trgEmployeeDelete ON dbo.Employee
    FOR DELETE
    AS

    INSERT INTO dbo.EmployeeArchive (EmployeeArchiveUUID , SSN)
    SELECT EmployeeUUID , SSN
    FROM deleted




    "Gregory A. Beamer" <> wrote in message
    news:Xns9C4E64D48FA2Bgbworld@207.46.248.16...
    > =?Utf-8?B?YnRodW1iZXI=?= <> wrote in
    > news::
    >
    >> I need to delete a record form a database, but I don't want to lose
    >> that record. I was told I could set a flag, so the question is how do
    >> you do that?
    >>

    >
    > There are two ways to set up a logical delete.
    >
    > 1. Add a field in the table called IsDeleted. This will be a bit type (SQL
    > Server) or a Boolean type (Access - hopefully you are not using Access).
    > The addition is:
    >
    > ALTER TABLE Table1
    > ADD
    > IsDeleted bit default 0 NOT NULL
    > GO
    >
    > You then have to alter your SQL so it respects this:
    >
    > AND IsDeleted = 0
    >
    > Except on the page where you can "undelete" an item, which would look for
    > deleted items only.
    >
    > AND IsDeleted = 1
    >
    > To delete, the command is like this:
    >
    > UPDATE Table1
    > SET IsDeleted = 1
    > WHERE ID = @id
    >
    > 2. Create a trigger that archives the "deleted file" To do this, you
    > create
    > a table exactly like the original table, but you call it _History.
    >
    > CREATE TABLE Table1_History
    > (
    > -- Fields go here
    > )
    > GO
    >
    > You can then set up a trigger:
    >
    > CREATE TRIGGER trgTable1Delete ON Table1
    > FOR DELETE
    > AS
    >
    > INSERT INTO Table1_History
    > (
    > -- Field names here
    > )
    > SELECT *
    > FROM deleted
    >
    > This will completely remove the record, but will make a copy in a
    > "history" table, so it is a better option if you have already heavily
    > invested in queries against this table.
    >
    > You can restore by reversing the delete (ie, deleting from history and
    > inserting into the original table).
    >
    > --
    > Gregory A. Beamer
    > MVP; MCP: +I, SE, SD, DBA
    >
    > Twitter: @gbworld
    > Blog: http://gregorybeamer.spaces.live.com
    >
    > *******************************************
    > | Think outside the box! |
    > *******************************************
    sloan, Jul 20, 2009
    #4
  5. "sloan" <> wrote in
    news::

    > I should have followed up, but the "History" idea is alot cleaner than
    > the SoftDelete flag/column.


    It depends on needs. Some times you have hte requirement to list all itesm in
    the table, deleted or not. In this case, the flag is cleaner. In general,
    however, a history table is cleaner, as you end up not having to worry about
    coding for a flag.

    > SNIPPED




    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 20, 2009
    #5
  6. bthumber

    sloan Guest

    //Some times you have hte requirement to list all itesm in
    > the table, //


    Agreed.

    One workaround option for this is a view that combines the data.



    GO

    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME
    = 'vwEmployeeMerged')
    begin
    DROP VIEW dbo.vwEmployeeMerged
    end
    GO

    CREATE VIEW dbo.vwEmployeeMerged

    AS

    select
    EmployeeUUID, SSN
    from
    dbo.Employee

    UNION ALL

    select
    EmployeeArchiveUUID as EmployeeUUID, SSN
    from
    dbo.EmployeeArchive


    GO

    print 'Put Back : vwEmployeeMerged'
    GRANT SELECT ON dbo.vwEmployeeMerged TO public
    GO









    "Gregory A. Beamer" <> wrote in message
    news:Xns9C4E6DBAD8954gbworld@207.46.248.16...
    > "sloan" <> wrote in
    > news::
    >
    >> I should have followed up, but the "History" idea is alot cleaner than
    >> the SoftDelete flag/column.

    >
    > It depends on needs. Some times you have hte requirement to list all itesm
    > in
    > the table, deleted or not. In this case, the flag is cleaner. In general,
    > however, a history table is cleaner, as you end up not having to worry
    > about
    > coding for a flag.
    >
    >> SNIPPED

    >
    >
    >
    > --
    > Gregory A. Beamer
    > MVP; MCP: +I, SE, SD, DBA
    >
    > Twitter: @gbworld
    > Blog: http://gregorybeamer.spaces.live.com
    >
    > *******************************************
    > | Think outside the box! |
    > *******************************************
    sloan, Jul 20, 2009
    #6
  7. "Mark Rae [MVP]" <> wrote in
    news::

    > "Gregory A. Beamer" <> wrote in
    > message news:Xns9C4E6DBAD8954gbworld@207.46.248.16...
    >
    >> In this case, the flag is cleaner.

    >
    > Don't some RDBMS provide this functionality natively...?
    >
    > IIRC, deleting a record in dBase / FoxPro doesn't actually delete it,
    > but just marks it internally as unavailable. Isn't there a PACK
    > command that you run to delete these "deleted" records permanently and
    > reclaim their disk space...?


    Technically, if we want to really geek out, you could conceivably get a
    deleted record from the transaction log. Until you have the final commit
    .... Now coding against the transaction log is a completely different
    creature. ;-)

    Couldn't help myself there. LOL

    I would imagine there are some that will offer this feature, but most
    RDBMS systems do not have the heavy handholding that something like
    FoxPro (or even ... shudder ... Access) might have. And when they do
    offer the free handholding, I am often leery of the system, as it
    requires a pretty tight box.


    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 20, 2009
    #7
  8. "sloan" <> wrote in
    news:#:

    > //Some times you have hte requirement to list all itesm in
    >> the table, //

    >
    > Agreed.
    >
    > One workaround option for this is a view that combines the data.
    >
    >
    >
    > GO
    >
    > IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
    > TABLE_NAME = 'vwEmployeeMerged')
    > begin
    > DROP VIEW dbo.vwEmployeeMerged
    > end
    > GO
    >
    > CREATE VIEW dbo.vwEmployeeMerged
    >
    > AS
    >
    > select
    > EmployeeUUID, SSN
    > from
    > dbo.Employee
    >
    > UNION ALL
    >
    > select
    > EmployeeArchiveUUID as EmployeeUUID, SSN
    > from
    > dbo.EmployeeArchive
    >
    >
    > GO
    >
    > print 'Put Back : vwEmployeeMerged'
    > GRANT SELECT ON dbo.vwEmployeeMerged TO public
    > GO
    >


    I would agree, but you can also get into a maintenance nightmare if this
    funcationality exists across the entire enterprise. And, if you don't
    know what you are doing, of course. ;-)

    We have a piece of software here that creates views on top of tables for
    encrypting the tables (no longer supported software, of course). It
    simply renames the table to {tablename}_base and then sticks the view
    with the original name. It also creates a view that does not decrypt the
    fields, so it is a similar problem.

    Short story, you can solve anything you might come in contact with. The
    view is a decent enough solution, but can create a bit of overhead that
    is unacceptable (very large apps with tons of users -- yes, most of us
    are not working for Google). With SQL Server, you can get past some of
    the overhead with Indexed views and the like, but that is a story for
    another day.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 20, 2009
    #8
  9. "Mark Rae [MVP]" <> wrote in news:uTHr$dXCKHA.4432
    @TK2MSFTNGP05.phx.gbl:

    > "Gregory A. Beamer" <> wrote in message
    > news:Xns9C4E75002983Fgbworld@207.46.248.16...
    >
    >> as it requires a pretty tight box.

    >
    > Nothing wrong with that... ;-)



    Is your office in your bedroom by any chance? ;-)


    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 21, 2009
    #9
  10. "Mark Rae [MVP]" <> wrote in news:#ovOyxlCKHA.1488
    @TK2MSFTNGP03.phx.gbl:

    > "Gregory A. Beamer" <> wrote in message
    > news:Xns9C4F85EFAAC19gbworld@207.46.248.16...
    >
    >>>> as it requires a pretty tight box.
    >>>
    >>> Nothing wrong with that... ;-)

    >>
    >> Is your office in your bedroom by any chance? ;-)

    >
    > You been on my webcam again...?


    ROFLMAO!


    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
    Gregory A. Beamer, Jul 23, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Harry Barker
    Replies:
    2
    Views:
    513
    Alf P. Steinbach
    Apr 19, 2006
  2. mldardy
    Replies:
    0
    Views:
    474
    mldardy
    Oct 4, 2010
  3. Kumar

    deleting DNS record using dnscmd.exe

    Kumar, Oct 14, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    164
    Kumar
    Oct 14, 2004
  4. xool

    Deleting curent record

    xool, Sep 11, 2003, in forum: ASP General
    Replies:
    8
    Views:
    136
  5. crea
    Replies:
    2
    Views:
    409
    Nobody
    Dec 28, 2012
Loading...

Share This Page