Monitor changes to data

You can monitor all the changes a single user makes to the database. Channel all changes through SPL routines that keep track of changes that each user makes. If you want to record each time the user acctclrk modifies the database, set up the database with the following privileges:

• You are the DBA of the database.
• All other 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 an SPL routine.
• Your SPL routine performs the deletion and records that a certain user makes a change.

Write an SPL routine similar to the following example (for a UNIX™ platform), which uses a customer number the user provides to update a table. If the user happens to be acctclrk, a record of the deletion is put in the file updates.

CREATE DBA PROCEDURE delete_customer(cnum INT)

DEFINE username CHAR(8);

DELETE FROM customer 
   WHERE customer_num = cnum;

IF username = 'acctclrk' THEN
   SYSTEM 'echo Delete from customer by acctclrk >>
/mis/records/updates' ;
END IF
END PROCEDURE; 

To monitor all the deletions made through the procedure, remove the IF statement and make the SYSTEM statement more general. The following procedure changes the previous routine to record all deletions:

CREATE DBA PROCEDURE delete_customer(cnum INT)

DEFINE username CHAR(8);
LET username = USER ;
DELETE FROM tbname WHERE customer_num = cnum;

SYSTEM
 'echo Deletion made from customer table, by '||username
||'>>/hr/records/deletes';

END PROCEDURE;