Preventing Deletes

Congratulations to Alejandro E. Casin III, an independent applications developer, and Niko Vrdoljak, a senior developer for Teched d.o.o. in Zagreb, Croatia. Nonoy won first prize of $100 for the best solution to the November Reader Challenge, "Preventing Deletes." Niko won second prize of $50. Here’s a recap of the problem and the solution to the November Reader Challenge.

Problem:

Hank, a database programmer, designs and maintains the human resources department's SQL Server 2000 database. Hank wants to prevent deletions from specific audit tables that contain sensitive data. He's already denied DELETE permissions on the tables to all users. What else can Hank do to prevent accidental deletions from the tables? The solution must prevent DELETE statements from removing rows from the tables. Assume that the database contains a table called EmployeesArchive that's similar to the Employees table in the Northwind database. The following script contains the audit table the problem requires.

USE northwindGOSELECT * INTO EmployeesArchive FROM EmployeesGO

Solution:

One of the ways that Hank can prevent deletions in this table is by using an INSTEAD OF trigger in SQL Server 2000. SQL Server replaces the DELETE statement with the INSTEAD OF DELETE trigger so that it’s executed instead. In the trigger body, Hank can check for @@rowcount value to determine if any rows were affected by the DELETE statement and raise an appropriate error message. The following script creates the INSTEAD OF delete trigger and tries to delete data from the EmployeesArchive table, but SQL Server executes the INSTEAD OF DELETE trigger and raises an error.

USE northwindGOCREATE TRIGGER DeleteEmployees ON EmployeesArchiveINSTEAD OF DELETEASBEGIN    IF @@rowcount > 0    BEGIN        RAISERROR( 'Audit rows for employees cannot be deleted!', 16, 2 )        ROLLBACK    ENDENDGO-- Try deleteing data from the table:DELETE EmployeesArchiveGO

DECEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the December Reader Challenge, "A Dashboard Application" (below). Submit your solution in an email message to [email protected] by November 18. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Terry, a systems analyst for the IT department, is developing a Web-based management dashboard. The data for the dashboard is stored in a SQL Server 2000 database. As part of one administrative function in the dashboard, the application requires the database to store user-defined attributes. The following code shows the table's schema including the relevant columns to store the attribute values for various objects in the system.

CREATE TABLE AttribVals (   Id int NOT NULL,   Val varchar(30) NOT NULL,   PRIMARY KEY( Id, Val ))

The column Id in the AttribVals table represents generic objects in the dashboard application. The following script creates sample data for the table.

INSERT INTO AttribVals (Id, Val)VALUES ( 1, 'A' )INSERT INTO  AttribVals (Id, Val)VALUES ( 1, 'B' )INSERT INTO  AttribVals (Id, Val)VALUES ( 1, 'C' )INSERT INTO  AttribVals (Id, Val)VALUES ( 2, 'B' )INSERT INTO  AttribVals (Id, Val)VALUES ( 3, 'A' )INSERT INTO  AttribVals (Id, Val)VALUES ( 3, 'C' )

Terry is designing a stored procedure that the Web page will call to manage the user-defined attributes. The Web page needs to pass a list of attributes and get the corresponding objects' Id value. Help Terry define the stored procedure with the required parameters. The stored procedure has to handle any number of attributes. For simplicity, assume that all values will be shorter than 8000. The stored procedure only has to retrieve the Id if all the specified attributes are configured for the Id value. Using the sample data shown earlier, specifying A and C as attribute values should return Ids 1 and 3; specifying B should return Ids 1 and 2.