-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- DISCLAIMER - Execute at your own risk. Read this script and thoroughly test first!!!
--
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)

USE CREDIT
go

-- BE CAREFUL!!! If you have a SalesTestTable this will DELETE it.
IF OBJECTPROPERTY(object_id('SalesTestTable'), 'IsUserTable') = 1
    DROP TABLE dbo.SalesTestTable
go
CREATE TABLE dbo.SalesTestTable
(
    InvNum        int        	NOT NULL    identity,
    InvDate        datetime  NOT NULL    CONSTRAINT SalesTestTableDateDflt DEFAULT getdate(),
    CustomerID int        	NULL        -- Foreign Key... and so forth
)
go
DECLARE @CustCounter    int
SELECT @CustCounter = 1
WHILE 1=1
BEGIN
    INSERT dbo.SalesTestTable (CustomerID) VALUES (@CustCounter)
    IF @CustCounter < 5
        SELECT @CustCounter = @CustCounter + 1
    ELSE
        SELECT @CustCounter = 1
END
go
SELECT count(*) FROM dbo.SalesTestTable
--SELECT * FROM dbo.SalesTestTable
go
DELETE dbo.SalesTestTable --what if someone were to highlight ONLY this line?
    WHERE invnum = 1234 -- even if they meant to highlight both?
-- Who is to blame? The system or database administrator that gave the user
-- the direct base table permissions... so, how can you avoid this?
-- Never give direct DELETE permissions to the base tables
go
-- Can prevent this with a VERY expensive (albeit automatic)
-- trigger
go
CREATE TRIGGER dbo.SalesTestTableDeleteTrigger
ON dbo.SalesTestTable
FOR DELETE
AS
DECLARE @RowCount    int
SELECT @RowCount = @@rowcount

IF @RowCount > 1
BEGIN
    RAISERROR ('You have attemped to delete %i rows. The SalesTestTable table does not allow multirow operations. Please retry DELETE on a row by row basis.', 16, 1, @rowcount) WITH LOG
    ROLLBACK TRANSACTION
    RETURN
END

-- OR you can arbitrarily set a MAX batch delete size?
-- IF @RowCount => 100
-- BEGIN
--     RAISERROR ('You have attemped to delete %i rows. The maximum number of rows that a single DELETE can support against this table is 100 rows. Please retry DELETE on a smaller set.', 16, 1, @rowcount) WITH LOG
--     ROLLBACK TRANSACTION
--     RETURN
-- END
go
-- OR would there be a performance improvement with INSTEAD OF Triggers?

CREATE TRIGGER dbo.SalesTestTableInsteadOFDeleteTrigger
ON dbo.SalesTestTable
INSTEAD OF DELETE
AS
DECLARE @RowCount    int
SELECT @RowCount = @@rowcount

IF @RowCount > 1
BEGIN
    RAISERROR ('Instead OF Trigger Error: You have attemped to delete %i rows. The SalesTestTable table does not allow multirow operations. Please retry DELETE on a row by row basis.', 16, 1, @rowcount) WITH LOG
    ROLLBACK TRANSACTION
    RETURN
END

-- OR you can arbitrarily set a MAX batch delete size?
-- IF @RowCount => 100
-- BEGIN
--     RAISERROR ('You have attemped to delete %i rows. The maximum number of rows that a single DELETE can support against this table is 100 rows. Please retry DELETE on a smaller set.', 16, 1, @rowcount) WITH LOG
--     ROLLBACK TRANSACTION
--     RETURN
-- END
go

-- The INSTEAD OF Trigger is a good safety catch BUT
-- you should really try to eliminate this from occuring 
-- in the first place!!!
go
CREATE PROCEDURE dbo.DeleteASalesTestTable
	@invnum	int
AS
DELETE dbo.SalesTestTable
	WHERE invnum = @invnum
-- While overly simplified (ie. no error handling, etc.) this 
-- still shows how a single user cannot delete more than one row
-- at a time.
go
EXEC dbo.DeleteASalesTestTable 1234
go

-- Creative (read: malicious) users are harder to prevent 
--      -> Application Roles might be an option
DECLARE @counter	int
SELECT @counter = MIN(invnum) FROM dbo.SalesTestTable
WHILE @counter < SELECT MAX(invnum) FROM dbo.SalesTestTable
BEGIN
	EXEC dbo.DeleteASalesTestTable @counter
	SELECT @counter = MIN(invnum) FROM dbo.SalesTestTable
			WHERE invnum > @counter
END
go