Using a trigger to prevent the deleting of records

The idea for this post came from a question I was asked about ways to stop a programmer from deleting records from a file using the STRSQL command. I thought this would be a good reason to use a combination of two solutions I have written about before.

My first thought was to use a validation trigger. Validation triggers must be executed before the insert, update, or delete occurs. That way I can determine if the STRSQL program was used, and if it was return an error code to the calling program.

Validation programs can be written in any language, and I have written them in both RPG and SQL. In this scenario I will be writing the trigger in SQL as, in my opinion, it is easier to write and, more importantly, easier for someone else to understand what is going on.

I am not going to bother to give the details of the file I am using to test this with. It is not important as this trigger will work with any file or table. What I will say is the file is called TESTFILE, resides in MYLIB, and contains a numeric field FIELD.

I can determine whether this delete operation is being performed using STRSQL by determining the program that the command calls, and then checking to see if it is the job's call stack. With the commands I have created myself I can use the DSPCMD command to see what program is called by the command. Alas, as STRSQL is a system object that information is not available to me. Determining the name of the program called by the STRSQL became the hardest part of this whole process. After a long time of checking and comparing my job's call stack I determined that the program in question is QSQIMAIN.

As the trigger is in SQL it make sense that I use the Db2 for i table function STACK_INFO to check the call stack.

Time for me to put all of this together. Here is the code I placed in a source member, which I called BLKSTRSQL, for my trigger:

01 CREATE OR REPLACE TRIGGER MYLIB.BLCKSTRSQL 02 BEFORE DELETE ON MYLIB.TESTFILE 03 FOR EACH ROW 04 MODE DB2SQL 05 BEGIN 06 DECLARE COUNT INTEGER ; 07 SELECT COUNT(*) INTO COUNT 08 FROM TABLE(QSYS2.STACK_INFO()) A 09 WHERE PROGRAM_NAME = 'QSQIMAIN' ; 10 IF COUNT > 0 THEN 12 SIGNAL SQLSTATE 'U0042' 13 SET MESSAGE_TEXT = 'STRSQL cannot delete rows! ; 14 END IF ; 15 END ;

Line 1: This is the name of this trigger, BLCKSTRSQL. When this SQL statement is executed it will generate a CLE object in the library MYLIB.

Line 2: The trigger will execute before any delete operation to TESTFILE.

As I am not going to be validating any of the fields in the file I do not have to make mention of the them here.

Line 4: I am using DB2SQL as I do not need to use any of the columns from TESTFILE in this trigger.

Line 5: This is where the trigger code starts.

Line 6: I am declaring a variable within the trigger as an integer type. I could have defined it as any type of number, but as this will contain a count of rows retrieved from the STACK_INTO table function I know I am only going to get integer values returned.

Lines 7 – 9: I am using this Select statement to get a count of the number of rows in the STACK_INFO table function where the program name is equal to the name of the program that STRSQL calls. This number is placed in the variable COUNT, which I declared on line 6.

Lines 10 – 14: If that program was found in this job's call stack then the COUNT variable will be greater than zero. If it is I want to return to the program that caused the trigger to be executed a SQL state code, and a message. Any SQL state codes I create I always start with a "U" so that I, and others, know that it is a user generated code, and not a system generated one. Do notice that line 13 has to be terminated with a semi colon ( ; ) before the END IF.

Line 15: This is where the trigger code ends.

To create the trigger I use the Run SQL Statement command, RUNSQLSTMT.

RUNSQLSTM SRCFILE(MYLIB/DEVSRC) SRCMBR(BLKSTRSQL) COMMIT(*NONE)

And I get my trigger compile as an ILE C object:

Work with Objects Using PDM Library . . . . . MYLIB Position to . . . . . . . Position to type . . . . Opt Object Type Attribute Text BLKSTRSQL *PGM CLE SQL TRIGGER BLKSTRSQL

The real proof that I have this trigger correct is to try to delete a record from the file. When I use the delete statement in STRSQL I see:

DELETE FROM TESTFILE WHERE FIELD = 1 SQL trigger BLCKSTRSQL in MYLIB failed with SQLCODE -438 SQLSTATE

When I look in the job log to see what has caused SQL code to be -438 I find my message and SQL state code from the trigger:

Message STRSQL cannot delete rows! returned from SIGNAL, RESIGNAL, or RAISE_ERROR. SQL trigger BLKSTRSQL in MYLIB failed with SQLCODE -438 SQLSTATE U0042.

What about deleting a record if I use a SQLRPGLE program. Will that be blocked too? Here is my test program:

01 **free 02 exec sql DELETE FROM TESTFILE WHERE FIELD = 2 ; 03 dsply ('SQLCOD = ' + %char(SQLCOD)) ; 04 *inlr = *on ;

Line 2: This is where I will be deleting a record/row from the file.

Line 3: If the delete was blocked by the trigger then the SQL code, SQLCOD, will not be zero.

When I run the program the SQL code is displayed, confirming that the delete occurred successfully.

DSPLY SQLCOD = 0

As a final test I created this RPG program to delete the next record, FIELD = 3, from the file:

01 **free 02 dcl-f TESTFILE usage(*delete) ; 03 read TESTFILER ; 04 delete TESTFILER ; 05 *inlr = *on ;

Line 2: If you are not familiar with defining files in free format RPG you will see that I have defined the usage of the file as delete. If I do not define the file as being delete capable I cannot delete records from it.

I ran the program and the next record, FIELD = 3, was deleted.

Just stopping the rogue programmer from deleting records using STRSQL may not be enough. I may want to block them using STRSQL to insert or update records in the file too. I can modify the above trigger to include that too:

01 CREATE OR REPLACE TRIGGER MYLIB.BLKSTRSQL 02 BEFORE INSERT OR UPDATE OR DELETE ON MYLIB.TESTFILE 03 FOR EACH ROW 04 MODE DB2SQL 05 BEGIN 06 DECLARE COUNT INTEGER ; 07 SELECT COUNT(*) INTO COUNT 08 FROM TABLE(QSYS2.STACK_INFO()) A 09 WHERE PROGRAM_NAME = 'QSQIMAIN' ; 10 IF COUNT > 0 THEN 11 IF INSERTING THEN 12 SIGNAL SQLSTATE 'U0043' 13 SET MESSAGE_TEXT = 'STRSQL cannot insert rows!' ; 14 ELSEIF UPDATING THEN 15 SIGNAL SQLSTATE 'U0044' 16 SET MESSAGE_TEXT = 'STRSQL cannot update rows!' ; 17 ELSEIF DELETING THEN 18 SIGNAL SQLSTATE 'U0042' 19 SET MESSAGE_TEXT = 'STRSQL cannot delete rows!' ; 20 END IF ; 21 END IF ; 22 END ;

The differences are…

Line 2: This now includes INSERT and UPDATE, therefore, all file operations are covered.

Line 10 – 21: If the program STRSQL calls is in the call stack then the trigger does the following.

Lines 11 – 13: On insert it performs these lines.

Lines 14 – 16: On update it performs these lines. Notice how line 14 starts with a ELSEIF, rather than another IF statement.

Lines 17 – 19: On delete these lines are performed.

Line 20: The end here is for the IF-ELSEIF group that started on line 11.

Below are the SQL statements I entered into STRSQL and the messages I received copied from the job log.

INSERT INTO TESTFILE VALUES(8888) Message STRSQL cannot insert rows! returned from SIGNAL, RESIGNAL, or RAISE_ERROR. SQL trigger BLKSTRSQL in MYLIB failed with SQLCODE -438 SQLSTATE U0043. UPDATE TESTFILE SET FIELD = FIELD + 1000 Message STRSQL cannot update rows! returned from SIGNAL, RESIGNAL, or RAISE_ERROR. SQL trigger BLKSTRSQL in MYLIB failed with SQLCODE -438 SQLSTATE U0044. DELETE FROM TESTFILE WHERE FIELD = 4 Message STRSQL cannot delete rows! returned from SIGNAL, RESIGNAL, or RAISE_ERROR. SQL trigger BLKSTRSQL in MYLIB failed with SQLCODE -438 SQLSTATE U0042.

 

If I had a rogue programmer who was deleting records from a file to cover their malfeasance I would not consider blocking them from using STRSQL to update a file. I would have a strong talk with them, followed by possible disciplinary actions.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.