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.