Prevent single row delete with trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Prevent single row delete with trigger

Author

Topic

ecinna
Starting Member

1 Post

Posted - 2015-01-16 : 17:02:58

I have this table:


CREATE TABLE Workspaces (
AreaNr CHAR(2)
    CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
SpaceNr INT
    CONSTRAINT ck_spacenr CHECK (SpaceNr > 0 AND SpaceNr < 1001),
Area DECIMAL(2,1)
    CONSTRAINT ck_areal CHECK (area > 0),
CONSTRAINT ck_workspaces PRIMARY KEY (AreaNr, SpaceNr)
)

Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.

Current trigger:


CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS

BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;

IF @Count >= (SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('Workspaces') AND index_id = 1
)

BEGIN
RAISERROR('You cannot delete all rows from Workspaces!',16,1)
ROLLBACK TRANSACTION
RETURN;
END

END
GO

Desired result: I want to be able to prevent a delete on a single row on the table above, and I would be very thankful if someone could help me to alter the trigger above so that this could be fixed. Does anyone know how this can be done?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-19 : 12:29:48

CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS
BEGIN

IF @@ROWCOUNT = 1
BEGIN
RAISERROR('You cannot delete all rows from Workspaces!',16,1)
ROLLBACK TRANSACTION
END
RETURN;
END

END
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources