Άραγε πόσες φορές όταν γράφουμε ένα Delete Query μπορεί νά κάνουμε λάθος καί νά σβήσουμε στην βάση περισσότερες γραμμές από αυτές που θέλουμε.
Φαντάζομαι ότι αυτό μπορεί νά έχει συμβεί σέ όλους μας.Παρακάτω παρθέτω μία Stored Procedure μέσω της οποίας μπορούμε νά εκτελούμε το Delete Statment μας καί άν τυχόν έχουμε κάνει κάποιο λάθος η διαγγεγραμμένες εγγραφές έχουν αποθηκευτεί σέ ένα Deleted αντίγραφο του πίνακα που κάνουμε διαγραφή.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cmd_Delete_sp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Cmd_Delete_sp]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Cmd_Delete_sp
(
@DeleteStatment AS VARCHAR(2000), --Πέρασμα του Delete Statment σαν παράμετρο
@HasIdentity smallint, --1 Αν ο πίνακας διαθέτει identity
@Apply_Comment AS VARCHAR(1000), --Εισαγωγή Σχολίου
@ReturnErrMsg AS VARCHAR(2000) --Μύνημα λάθους κατά την εκτέλεση της procedure
)
AS
--ΔΗΛΩΣΗ ΜΕΤΑΒΛΗΤΩΝ
DECLARE @VarStr AS VARCHAR(2000), --Μεταβλητή για κατασκευή του Sql Statement
@VarSelect AS VARCHAR(2000), --Μεταβλητή για την δημιουργία του Select Statement
@VarTable AS VARCHAR(2000), --Μεταβλητή για την ονομασία του πίνακα των διαγγεγραμμένων εγγραφών
@VarPos AS INT --Μεταβλητή γιά την θέση των αποθχαρακτήρων
--Εδώ κάνουμε trim τα δεδομένα
SET @DeleteStatment = ' '+ LTRIM(RTRIM(@DeleteStatment)) + ' '
--Δημιουργούμε αντίγραφο της παραμέτρου @DeleteStatment για να εκτελέσουμε το 'SELECT' SQL STATEMENT
SET @VarSelect = @DeleteStatment
--Δημιουργία του 'SELECT' SQL από την παράμετρο @DeleteStatment
--έτσι δημιουργούμε άνα ακριβές αντίγραφο τών Record που πρόκειται νά διαγραφούν
SET @VarPos = CHARINDEX(' DELETE ', @VarSelect)
IF @VarPos > 0
BEGIN
IF CHARINDEX(' FROM ', @VarSelect, @VarPos) = 0
BEGIN
--Εδώ δημιουργούμε το αντίγραφο άν στην παράμετρο @DeleteStatment δέν έχουμε δηλώσει FROM π.χ. DELETE customers
IF CHARINDEX(' WHERE ',LTRIM(RTRIM(@VarSelect)))=0
BEGIN
SET @VarTable = LTRIM(RTRIM(SUBSTRING(@VarSelect, @VarPos + 7, 2000)))
SET @VarSelect = ' SELECT * FROM ' + @VarTable
SELECT @VarTable
END
ELSE
BEGIN
SET @VarStr = RTRIM(LTRIM(SUBSTRING(@VarSelect, CHARINDEX(' WHERE ', LTRIM(RTRIM(@VarSelect))) + 7, 2000)))
SET @VarTable = LTRIM(RTRIM( SUBSTRING(@VarSelect, @VarPos + 7, CHARINDEX(' WHERE ', LTRIM(RTRIM(@VarSelect)))- 7)))
SET @VarSelect = ' SELECT * FROM ' + @VarTable +' WHERE '+@VarStr
END
END
ELSE
BEGIN
--Εδώ δημιουργούμε το αντίγραφο άν στην παράμετρο @DeleteStatment έχουμε δηλώσει FROM π.χ. DELETE FROM customers
SET @VarStr = RTRIM(LTRIM(SUBSTRING(@VarSelect, CHARINDEX(' FROM ', @VarSelect) + 6, 2000)))
IF CHARINDEX(' ', @VarStr) = 0
BEGIN
SET @VarSelect = ' SELECT * FROM ' + @VarStr
SET @VarTable = @VarStr
END
ELSE
BEGIN
SELECT SUBSTRING(@VarStr, 1, CHARINDEX(' ', @VarStr)) ,@VarStr
SET @VarTable = SUBSTRING(@VarStr, 1, CHARINDEX(' ', @VarStr))
SET @VarSelect = REPLACE(@VarSelect, ' DELETE ', ' SELECT * ')
END
END
END
ELSE
BEGIN
--Επιστρέφουμε μύνημα σφάλματος εφόσον δέν έχει δηλωθεί η σύνταξη DELETE
SET @ReturnErrMsg = 'Δέν υπάρχει η έκφραση DELETE στην δήλωση της αρχικής παραμέτρου: Συντακτικό σφάλμα'
RETURN -1
END
--Παρακάτω εκτελούμε το δημιουργηθέν Select Statement για νά εισάγουμε τις εγγραφές που πρόκειται νά διαγραφούν σέ ένα αντίγραφο του πίνακα
IF CHARINDEX('[',@VarTable)=0
BEGIN
SET @VarTable = 'DELETEDROWS_' + @VarTable
END
ELSE
BEGIN
SET @VarTable = '[DELETEDROWS_' + REPLACE(@VarTable,'[','')
END
--Έλεγχος ύπαρξης αντιγράφου του πίνακα από τον οποίο πρόκειται να κάνουμε διαγραφή
IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @VarTable ) > 0
BEGIN
--Ο πίνακας ήδει υπάρχει
--Δημιουργία του Insert Statment
IF @HasIdentity=1
BEGIN
DECLARE @columnList varchar(1000),@columnName varchar(255),@execSql nvarchar(1000)
SET @columnList=''
DECLARE cur1 CURSOR FOR
SELECT b.name from sysobjects a,syscolumns b WHERE a.id=b.id and a.type='u' and <A href="mailto:a.name=@VarTable">a.name=@VarTable</A>
OPEN cur1
FETCH NEXT FROM cur1 INTO @columnName
WHILE @@fetch_status<>-1
BEGIN
SELECT @columnList=@columnList+','+@columnName
FETCH NEXT FROM cur1 INTO @columnName
END
CLOSE cur1
DEALLOCATE cur1
SET @columnList=substring(@columnList,2,len(@columnList))
SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' INSERT INTO ' + @VarTable + ' (<A href="mailto:'+@columnList+'">'+@columnList+'</A>) SELECT *, (SELECT MAX(SEQUENCE_ID)+1 FROM ' + @VarTable + '), ''' + @Apply_Comment + ''' ')
END
ELSE
BEGIN
SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' INSERT INTO ' + @VarTable + ' SELECT *, (SELECT MAX(SEQUENCE_ID)+1 FROM ' + @VarTable + '), ''' + @Apply_Comment + ''' ')
END
END
ELSE --Όταν ο πίνακας δέν υπάρχει
BEGIN
--Δημιουργούμε το Create αυτού
SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' SELECT *, 1 AS SEQUENCE_ID, ''' + @Apply_Comment + ''' AS COMMENT INTO ' + @VarTable + ' ' )
END
BEGIN TRANSACTION DELETE_TRANSACTION
--Εκτέλεση του δυναμικού SELECT SQL το οποίο Δημιουργεί και εισάγει τις εγγραφές που προκειται να διαγραφούν στο αντίγραφο του πίνακα
EXEC(@VarSelect)
--Έλεγχος για λάθη
IF @@ERROR > 0
BEGIN
SET @ReturnErrMsg = 'Λάθος κατά την δημιουργία ή εισαγωγή στο αντίγραφο'
ROLLBACK TRANSACTION DELETE_TRANSACTION
RETURN -1
END
--Εκτέλεση της εντολής διαγραφής
EXEC (@DeleteStatment)
--Έλεγχος για λάθη
IF @@ERROR > 0
BEGIN
SET @ReturnErrMsg = 'Λάθος κατά την εκτέλεση της διαγραφής'
ROLLBACK TRANSACTION DELETE_TRANSACTION
RETURN -1
END
ELSE
BEGIN
--Επιτυχής εκτέλεση
COMMIT TRANSACTION DELETE_TRANSACTION
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Παράδειγμα σε Northind.
'Οταν ο πίνακας που θα γίνει διαγραφή έχει IDENTITY πρέπει νά τον εκτελέσουμε όπως παρακάτω
'DELETEDROWS_ πάντα το πρόθεμα του νέου πίνακα
SET IDENTITY_INSERT [DELETEDROWS_Order details] ON
go
Cmd_Delete_sp 'DELETE [Order details] WHERE OrderID>=1 AND OrderID<=1000',1,' ',' '
go
SET IDENTITY_INSERT [DELETEDROWS_Order details] OFF
όταν δέν έχει εκτελείται
Cmd_Delete_sp 'DELETE [Order details] WHERE OrderID>=1 AND OrderID<=1000',1,' ',' '