Wednesday, September 27, 2023

Deleting records from a file without the delete trigger executing

I have a file that has a "delete trigger" on it. I need to delete all of the records in the file without the "delete trigger" inserting data into its output file. How could I do that without removing the trigger from the file?

I am going to show you how.

Before I do I am going to need a file with data. I have created a file, TESTFILE, in my library, MYLIB, that has ten records in it:

01  SELECT * FROM TESTFILE

TEXT
--------------
First record
Second record
Third record
Fourth record
Fifth record
Sixth record
Seventh record
Eighth record
Ninth record
Tenth record

I am going to create a SQL trigger that is only going to be executed when a record is delete from the file. This is the code for my "delete trigger":

01  CREATE OR REPLACE TRIGGER MYLIB.TESTFILE_TRIGGER
02  AFTER DELETE ON MYLIB.TESTFILE
03  REFERENCING OLD ROW AS OLD
04  FOR EACH ROW MODE DB2ROW

05  BEGIN
06    IF DELETING THEN
07      INSERT INTO MYLIB.TESTFILTRG
08        VALUES(CURRENT TIMESTAMP,
09               JOB_NAME,
10               OLD.TEXT) ;
11    END IF ;
12  END ;

Line 1: My trigger will be called TESTFILE_TRIGGER.

Line 2: It will only be executed when a record is deleted from TESTFILE in MYLIB.

Line 3: I am going to be using "OLD" to identify the old row/record, and this is used to prefix the file's fields too.

Line 4: This means that the trigger will be executed after each record operation.

Line 5: This marks the beginning of the trigger's code.

Lines 6 - 11: If I am deleting a row from TESTFILE, then I am inserting a row into the file TRESTFILTRG. The output file contains fields for the current timestamp, job name, and the value in TESTFILE's field TEXT.

Line 12: Code for the trigger ends.

After having created the trigger how can I make sure that it has been added to the file?

I could use the DSPFD command, but where is the fun in using that? Instead I am using the SYSVIEWS SQL View. The statement below will return the information I want to check:

01  SELECT SYSTEM_EVENT_OBJECT_SCHEMA AS "Library",
02         SYSTEM_EVENT_OBJECT_TABLE AS "File",
03         TRIGGER_SCHEMA AS "Trg lib",
04         TRIGGER_NAME AS "Trg name",
05         EVENT_MANIPULATION AS "Trg type",
06         ACTION_TIMING "When"
07    FROM QSYS2.SYSTRIGGERS
08   WHERE SYSTEM_EVENT_OBJECT_SCHEMA = 'MYLIB'
09     AND SYSTEM_EVENT_OBJECT_TABLE = 'TESTFILE'

Line 1 – 6: These are the columns I want to check. I think the long column names describe what each column contains, so I am not going to explain what they are.

Line 7: Get the results from the SYSTRIGGERS View.

Lines 8 and 9: I only want the result for my file in my library.

The results are:

Lib      File      Trg lib  Trg name          Trg type  When
-------  --------  -------  ----------------  --------  -----
MYLIB    TESTFILE  MYLIB    TESTFILE_TRIGGER  DELETE    AFTER

I see that the trigger has been added as a "delete trigger" that will be executed after the delete has occurred to the record in TESTFILE.

Now if I delete all the records from TESTFILE with the following statement:

01  DELETE FROM MYLIB.TESTFILE

I can check for the number of records in TESTFILE:

01  SELECT COUNT(*) FROM TESTFILE

00001
------
     0

No records in the file is what I wanted. But what about the trigger output file, TESTFILTRG? Does it contain any data?

01  SELECT COUNT(*) FROM TESTFILTRG

00001
------
    10

Alas, it has ten records as they are copies of the records I deleted from TESTFILE.

There is another way I can do this without the "delete trigger" executing, the SQL TRUNCATE statement. It has five parameters:

  1. Name of the table, library is optional. I like to give the library name to make really sure I am using the file I expect.
  2. Whether to release or keep the storage in the file
  3. Whether to ignore the "delete trigger" or give an error if there is on the file
  4. Restart the value of the identity column. Not needed in this example as the file does not have an identity column
  5. Whether the statement is performed immediately, and its changes cannot be rolled back by commitment control

I copy from the saved data I have to insert ten records into TESTFILE, and I clear TESTFILTRG.

My TRUNCATE statement looks like:

01  TRUNCATE TABLE MYLIB.TESTFILE
02           DROP STORAGE
03           IGNORE DELETE TRIGGERS
04           IMMEDIATE

Line 1: Yes, I want to delete records from this file.

Line 2: This will release the storage from the file and is made available. This is the default, and I think it is a good idea to include it in the statement so that others will know what it will do with the storage.

Line 3: I do not want the "delete trigger" to be executed. This is the default, and I give it here so it is on record that I wanted to ignore the "delete trigger".

Line 4: I want the statement to be executed immediately, and I don't want it to be possible to retrieve the delete records via commitment control.

With only ten records the delete takes a second.

And what of the record counts in the files?

In TESTFILE I have no records:

01  SELECT COUNT(*) FROM TESTFILE

00001
------
     0

And, to my relief, there are also no records in the trigger output file:

01  SELECT COUNT(*) FROM TESTFILTRG

00001
------
     0

TRUNCATE has given me a way to do what I wanted, to delete records from one file and not have them added to the "delete trigger's" output file.

 

You can learn more about the SQL TRUNCATE statement from the IBM website here.

 

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

1 comment:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.