Restricting Changes to Data

When you use SPL routines, you can restrict changes made to a table. Channel all changes through an SPL routine. The SPL routine makes the changes, rather than users making the changes directly. If you want to limit users to deleting one row at a time to ensure that they do not accidentally remove all the rows in the table, set up the database with the following privileges:

• You are the DBA of the database.
• All the users have the Connect privilege to the database. They might have the Resource privilege. They do not have the Delete privilege (for this example) on the table being protected.
• You use the DBA keyword to create the SPL routine.
• Your SPL routine performs the deletion.

Write an SPL procedure similar to the following one, which uses a WHERE clause with the customer_num that the user provides, to delete rows from the customer table:

CREATE DBA PROCEDURE delete_customer(cnum INT)

DELETE FROM customer 
   WHERE customer_num = cnum;

END PROCEDURE;