Hi all

I would like to know if its possible to "Save" records when they get deleted.

For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...

In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.

All this should go to a seperate database or seperate table, it doesnt really matter.

My delete query:
DELETE FROM [tblUsers] WHERE [UserID] = @UserID

 There are two ways of accomplishing this:

• Use a stored procedure to select the existing row and insert it into the archive table, before deleting the row.
• Use a trigger tied to the delete
  

Hi Tatworth

I'm still learning by example, as this SQL stuff is all new to me.Could you please explain to me in detail, and maybe show by an example how to accomplish this... You can use a simple table, like tblUsers, with UserID, Name, Surname as columns.

Thanks

 Using tables and stored procedures

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Integer Key',
     @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Table1',
     @level2type=N'COLUMN', @level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of something',
     @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Table1',
     @level2type=N'COLUMN', @level2name=N'name'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1Log]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table1Log](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [table1_id] [int] NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_Table1Log] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Integer Key',
  @level0type=N'SCHEMA', @level0name=N'dbo',
  @level1type=N'TABLE', @level1name=N'Table1Log',
  @level2type=N'COLUMN', @level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id from table1' ,
  @level0type=N'SCHEMA', @level0name=N'dbo',
  @level1type=N'TABLE', @level1name=N'Table1Log',
  @level2type=N'COLUMN', @level2name=N'table1_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of something' ,
  @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
  @level1name=N'Table1Log', @level2type=N'COLUMN', @level2name=N'name'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Table1Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_Table1Delete]
(
  @id   INT
) AS
--  Purpose:
--     Delete record on Test1 table
--  Parameters:
--    id  -
--  History:  
--  08Mar2008 Clive Original coding
SET NOCOUNT ON
BEGIN TRANSACTION
  INSERT INTO Table1Log(Table1_Id, NAME) SELECT Id, Name FROM Table1 WHERE id = @id
  DELETE Table1 WHERE id = @id
COMMIT
RETURN
-------------- this is the end ----------------
'
END

Try running the following:

INSERT INTO Table1(Name) VALUES ('Alf')
INSERT INTO Table1(Name) VALUES ('Bert')
INSERT INTO Table1(Name) VALUES ('Charles')

usp_Table1Delete 2
SELECT * FROM Table1
SELECT * FROM Table1Log

You can use OUTPUT clause if you are using Sql Server 2005.

Create a standalone table tblUsersArchive to archive tblUsers with same columns as your original table. (Right click on the table from Management Studio and Script the table as>> Create to>>New Query Edit Window, change the table name and remove the old key and constrains. You can also add a new key here with an identity field. Make sure the archive table does not have triggers or constrains on it. Also no key relationships are defined for all involved columns.

Here is the sample code:

WHERE [UserID] = @UserID

A much simpler approach would be to add a bit field to the existing table and call it Deleted, then set it to 1 or true.  You could add a couple of additional fields to record who did this and when, if you haven't already got LastModifiedBy and LastModifiedDate columns.

 If the requirement is for auditing purposes, a delete trigger would be ideal. With triggers, there wont be any change to your existing code. If you have deletes in multiple stored procedures, all of them will get handled from a single point.
 

Hi XPSCodes

Indeed it will, but how does one convert your select / update / delete statement to a trigger - where do you begin? I would've added the query to VWD2008, but it also makes sense to add it to SQL in the backend.

I've managed to get some code of another website, which is easy to read, and easy to understand. I've also added a ArchiveID to the new table, which will Indentity Specification by 1 each time a record is deleted in the main table, and inserted in the archive table. So, a record with the same details could be deleted twice, with diffrent ArchiveID's.

The Code:
USE SurveyBonusDB

DELETE FROM tblUsers
OUTPUT Deleted.UserID,
Deleted.Name,
Deleted.Surname,
Deleted.Initials,
Deleted.PayrollNumber,
Deleted.LocationID,
Deleted.RoleID,
Deleted.DateAdded
INTO tblUsersArchive
WHERE UserID = '7'

GO

-- Verification in the base table
SELECT *
FROM tblUsers
GO

-- Verification in the auditing table
SELECT *
FROM tblUsersArchive
GO

This is how it the trigger needs to be setup. In this example, you can change the AFTER DELETE clause to AFTER UPDATE,DELETE for auditing updates. You can even add parameters like SYSTEM_USER, a timestamp  column etc to keep track of who deleted and when it was deleted etc.

-- A sample  users table

Create Table tblUsers
( UserID INT Identity(1,1) NOT NULL,
  UserName varchar(20) NOT NULL)


-- A sample archive table
Create Table tblUsers_Archive
( Archie_ID INT Identity(1,1) NOT NULL,
  UserID INT NOT NULL,
  UserName varchar(20) NOT NULL)

-- A simple trigger on tblUsers.

CREATE TRIGGER tr_tblUsers
   ON  [dbo].tblUsers
   AFTER DELETE
AS
BEGIN
    Insert tblUsers_Archive
    Select * From Deleted
END
-- Inserting some sample data

INSERT tblUsers
Select 'AAAA' UNION ALL
Select 'BBBB' UNION ALL
Select 'CCCC' UNION ALL
Select 'DDDD' UNION ALL
Select 'EEEE' UNION ALL
Select 'FFFF'

--Deleting 2 records. This should get inserted in archive table
Delete tblUsers Where Username='AAAA'
Delete tblUsers Where Username='BBBB'

--Select from archive table

Select * From tblUsers_Archive