Recycle feature in MS SQL Server 2000
Recycle feature in MS SQL Server 2000
We can find a lot of stuff on SQL Select, Insert and etc.
There are few articles/discussions on SQL ‘DELETE’.
Here is an idea about SQL ‘DELETE’ statement.
If the data is deleted from the tables without using
Transactions, there is no way for Developers to retrieve it back, probably DBAs
or certain tools may retrieve it back for you.
Here is an effort to give a little leverage to Developers in dealing with
record deletion.
This Stored Procedure accepts DELETE SQL statement and
comment for deletion as parameters. Copies
the deleting records to another table with the same name but prefixed by
“Recycle_”. Along with copying
the deleting records, it stores two more information for back reference i.e.
comment for deleting the record, incremental number that uniquely identifies a
record or set of records that got deleted in each DELETE SQLs.
Algorithm:
·
Accept DELETE SQL Statement, Comment to be put to all the deleting
records.
·
Check for the ‘DELETE ‘ key word in DELETE Sql parameter
·
Construct the Select/Insert SQL statement in order to insert the
deleting rows using ‘SELECT INTO’ or ‘INSERT..SELECT’ SQLs.
·
Execute the constructed “Insert/Select” SQL.
o
If the Recycle copy of the deleting records table does not exist
then create one.
o
Insert all the deleting records to recycle copy, with Transaction
Number and Comment i.e. supplied by the Developer.
·
Finally execute the actual SQL DELETE.
Expansion:
You can store more information about the deleting rows by adding
more columns in Recycle table like Database Login ID, Deleted Date and Time,
DELETE SQL Statement and etc. depending on the requirement.
SP Script:
CREATE
PROCEDURE SP_RDELETE
(@SDELETE
AS VARCHAR(2000),
--DELETE
SQL STATEMENT AS PARAMETER
@SCOMMENT
AS VARCHAR(1000),
--COMMENT/TITLE
FOR THE DELETING RECORD(S)
@ERRORMESSAGE
AS VARCHAR(2000) --ERROR
MESSAGE IF ANY DURING THE SP EXECUTION
)
AS
--VARIABLE
DECLARATION
DECLARE
@SSTR AS VARCHAR(2000),
--VARIABLE TO CONSTRUCT SQL STATEMENTS
@SSELECT
AS VARCHAR(2000),
--VARIABLE TO CONSTRUCT 'SELECT' SQL
@STABLE
AS VARCHAR(2000),
--VARIABLE TO STORE TABLE NAME
@SPOS
AS INT
--VARIABLE TO STORE CHARACTER POSITIONS
--TRIMMING
OF DATA
SET
@SDELETE = ' '+ LTRIM(RTRIM(@SDELETE)) + ' '
--COPY
OF @SDELETE PARAMETER TO EXECUTE 'SELECT' SQL STATEMENT
SET
@SSELECT = @SDELETE
--CONSTRUCT
'SELECT' SQL FROM @SDELETE PARAMETER
--USING
'SELECT' SQL COPY THE DELETING RECORDS TO RECYCLE TABLE COPY
SET
@SPOS = CHARINDEX(' DELETE ', @SSELECT)
IF
@SPOS > 0
BEGIN
IF CHARINDEX(' FROM ', @SSELECT, @SPOS) = 0
BEGIN
SET @STABLE = LTRIM(RTRIM(SUBSTRING(@SSELECT, @SPOS + 7, 2000)))
SET @SSELECT = ' SELECT * FROM ' + @STABLE
END
ELSE
BEGIN
SET @SSTR = RTRIM(LTRIM(SUBSTRING(@SSELECT, CHARINDEX(' FROM ', @SSELECT)
+ 6, 2000)))
IF CHARINDEX(' ', @SSTR) = 0
BEGIN
SET @SSELECT = ' SELECT * FROM ' + @SSTR
SET @STABLE = @SSTR
END
ELSE
BEGIN
SET @STABLE = SUBSTRING(@SSTR, 1, CHARINDEX(' ', @SSTR))
SET @SSELECT = REPLACE(@SSELECT, ' DELETE ', ' SELECT * ')
END
END
END
ELSE
BEGIN
SET @ERRORMESSAGE = 'NO DELETE KEYWORD IN THE SDELETE PARAMETER'
RETURN -1
END
--USING
'SELECT' SQL COPY THE DELETING RECORDS TO RECYCLE TABLE COPY
SET
@STABLE = 'RECYCLE_' + @STABLE
--CHECK
WHETHER RECYCLE TABLE FOR DELETING RECORDS TABLE ALREADY EXIST
--IF
DOES NOT EXIST CREATE ONE
IF
(SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = @STABLE ) > 0
BEGIN
--TABLE
ALREADY EXIST
--BUILDING
THE SQL: INSERT INTO EXISTING RECYLE TABLE
--USING
'INSERT..SELECT' SQL
SET @SSELECT = REPLACE (@SSELECT, ' SELECT * ', ' INSERT INTO ' + @STABLE
+ ' SELECT *, (SELECT MAX(TRANSNO)+1 FROM ' + @STABLE + '), ''' +
@SCOMMENT + ''' ')
END
ELSE
--TABLE
DOES NOT EXIST
BEGIN
--BUILDING THE SQL: CREATE TABLE USING 'SELECT INTO' SQL
SET @SSELECT = REPLACE (@SSELECT, ' SELECT * ', ' SELECT *, 1 TRANSNO,
''' + @SCOMMENT + ''' COMMENT INTO ' + @STABLE + ' ' )
END
BEGIN
TRANSACTION TRANS_SP_DELETE
--EXECUTE
THE CONSTRUCTED SELECT SQL THAT CREATES/INSERTS RECYCLE TABLE
EXEC(@SSELECT)
--CHECK
FOR ERRORS
IF
@@ERROR > 0
BEGIN
SET @ERRORMESSAGE = 'ERROR WHILE CREATING/UPDATING RECYCLE TABLE'
ROLLBACK TRANSACTION TRANS_SP_DELETE
RETURN -1
END
--EXECUTE
THE ACTUAL DELETE STATEMENT NOW
EXEC
(@SDELETE)
--CHECK
FOR ERRORS
IF
@@ERROR > 0
BEGIN
SET @ERRORMESSAGE = 'ERROR WHILE EXECUTING PASSED DELETE SQL STATEMENT'
ROLLBACK TRANSACTION TRANS_SP_DELETE
RETURN -1
END
ELSE
BEGIN
--COMPLETED
SUCCESSFULLY
COMMIT TRANSACTION TRANS_SP_DELETE
RETURN 0
END
Parameters:
|
Parameter
Name
|
Description
|
|
@SDELETE
|
Actual
DELETE SQL statement
|
|
@SCOMMENT
|
Comment/Title
for the deleting records. This
will be stored in “Comment” column of Recycle copy of the table.
|
|
@ErrorMessage
[INPUT/OUTPUT Parameter]
|
Any
error during the SP execution.
|
Returns
Returns
0 on successful execution.
Returns
–1 on unsuccessful execution
with error message in @ErrorMessage input/output parameter
Limitation
of SP
Currently
the SP is designed to handle only certain kind of SQL ‘DELETE’ statements.
The
supported SQL ‘DELETE’ types are below.
A.
Use DELETE with no parameters E.g. DELETE
authors OR DELETE from authors
B.
Use DELETE
on a set of rows E.g. DELETE FROM authors WHERE au_lname
= 'McBadden'
Effort is on to include all type of ‘DELETE’ statements, some of the other types of DELETE statements are a) Use DELETE on the current row of a cursor b)Use DELETE based on a subquery or use the Transact-SQL extension c)Use DELETE and a SELECT with the TOP Clause
Usage
Ø
While fixing the Production
related issues, use this SP instead of DELETE SQL to keep the history of
existing/bad records.
Ø
To recycle the deleted
records in future.
Ø
To Archive the old OR very
rarely used records, thereby increasing the performance of the table
transactions.
Example
Check the Screen shot for
example.
| 
Download article