Cheetah spot by spot: Multiple triggers for same event/table

Welcome to another IDS 11.10 (Cheetah) new feature close-up. This time we'll take a look at the possibility of creating multiple triggers on the same table and same event.
Up to IDS 11 you could have only one INSERT/UPDATE/DELETE/SELECT trigger for each table. That means that if you wanted to do more in one of the events for which you already had a trigger you would have to change the existing trigger definition.

Now, with Cheetah, you can simply create another trigger. When the event happens both triggers will be fired and their actions will be done. It's a simple concept, but you should be aware of one important aspect: The sequence of execution of the triggered actions.

There is no guarantee to which trigger will execute first, but all the BEFORE actions will be done before all FOR EACH ROW actions, and finally the AFTER actions will be executed. So, the events have a pre-defined order to be executed, but you can't tell which trigger's event will be fired first. I've set up a test case that tries to show this issues. Let's take a look:

1 drop procedure trigger_change;
2 drop table test;
3 drop table action_log;
4 drop procedure trigger_proc;
5 CREATE TABLE test
6 (
7       col1 INTEGER,
8       col2 CHAR(20)
9 ) LOCK MODE ROW;
10
11 CREATE TABLE action_log
12 (
13      trig_name CHAR(20),
14      trig_event CHAR(20),
15      trig_action CHAR(20),
16      trig_seq SERIAL
17 ) LOCK MODE ROW;
18
19 CREATE PROCEDURE trigger_change () REFERENCING OLD AS old NEW AS new FOR test
20      LET new.col1 = 0;
21 END PROCEDURE;
22 CREATE PROCEDURE trigger_proc (t_name CHAR(20), t_action CHAR(20))
23      DEFINE t_event CHAR(20);
24      LET t_event = NULL;
25      IF t_action = 'FOR EACH ROW'
26      THEN
27              SELECT
28              CASE
29                      WHEN DELETING THEN "DELETING"
30                      WHEN UPDATING THEN "UPDATING"
31                      WHEN INSERTING THEN "INSERTING"
32                      WHEN SELECTING THEN "SELECTING"
33              END
34              INTO t_event
35              FROM sysmaster:sysdual;
36      END IF
37      INSERT INTO action_log VALUES (t_name, t_event, t_action, 0);
38 END PROCEDURE;
39
40 CREATE TRIGGER ti_1_test INSERT ON test
41 BEFORE
42 (
43      EXECUTE PROCEDURE trigger_proc('ti_1_test','BEFORE')
44 )
45 FOR EACH ROW
46 (
47      EXECUTE PROCEDURE trigger_proc('ti_1_test','FOR EACH ROW'),
48      EXECUTE PROCEDURE trigger_change() WITH TRIGGER REFERENCES
49 )
50 AFTER
51 (
52      EXECUTE PROCEDURE trigger_proc('ti_1_test','AFTER')
53 );
54
55 CREATE TRIGGER ti_2_test INSERT ON test
56 BEFORE
57 (
58      EXECUTE PROCEDURE trigger_proc('ti_2_test','BEFORE')
59 )
60 FOR EACH ROW
61 (
62      EXECUTE PROCEDURE trigger_proc('ti_2_test','FOR EACH ROW')
63 )
64 AFTER
65 (
66      EXECUTE PROCEDURE trigger_proc('ti_2_test','AFTER')
67 );
68
69 INSERT INTO TEST VALUES(1,"one...");
70 SELECT * FROM test;
71 SELECT * FROM action_log;

The execution result is:

cheetah@PacMan.domus.online.pt:informix-> dbaccess stores_demo multiple_triggers.sql
Your evaluation license will expire on 2007-11-24 00:00:00

Database selected.


Routine dropped.


Table dropped.


Table dropped.


Routine dropped.


Table created.


Table created.


Routine created.


Routine created.


Trigger created.


Trigger created.


1 row(s) inserted.



      col1 col2

         0 one...

1 row(s) retrieved.



trig_name            trig_event           trig_action             trig_seq

ti_1_test                                 BEFORE                         1
ti_2_test                                 BEFORE                         2
ti_1_test            INSERTING            FOR EACH ROW                   3
ti_2_test            INSERTING            FOR EACH ROW                   4
ti_1_test                                 AFTER                          5
ti_2_test                                 AFTER                          6

6 row(s) retrieved.


Database closed.

So, let's make a line by line examination of the script. Hopefully, at the end, and after looking at the results, you'll get a clear picture of what's new in this area.

Lines 1 to 18 just create two tables: "test" table will be used as the triggers object and "action_log" will save the name of the trigger (in a minute...), the event that was being executed (BEFORE, FOR EACH ROW, AFTER), the action (INSERT,DELETE...) and the sequence by which they were called.

Lines 19 to 21 create what is called a triggered UDR. This UDR must be created in SPL, but can call other UDRs in another languages (C, JAVA...). This UDR must contain the REFERENCING and FOR clauses to be considered a triggered UDR. The referencing clause is similar to the same clause in triggers. The FOR clause defines which table's triggers can call this UDR in the FOR EACH ROW clause. I've included this to show that you can change the values that the user process used as VALUES in an INSERT statement for example. So, actually you can replace the values given by the client process.

The next lines (22-38) contain another procedure that will be called by the triggers and receives the trigger name and action. This procedure contains two new things:

1. It uses the new operators "DELETING", "INSERTING", "UPDATING" and "SELECTING". These new operators can be used in the context of a FOR EACH ROW action and return true if the trigger event is respectively DELETE, INSERT, UPDATE and SELECT
2. It also uses a new IDS 11.10 feature, the sysmaster:sysdual table. This is an Oracle ©right; compatibility feature. You could create a "dual" table in any Informix version, so this isn't really a big feature...

This procedure logs the data in the action_log table, inserting a zero into the trig_seq field. This forces IDS to generate a value by increasing the last one inserted. So we will get the execution order of the triggered actions by looking at this fields.

Lines 40-68 include the two trigger definitions. They are both INSERT triggers and they call the second procedure in each of it's actions. The first one also calls the first procedure (trig_change) to show that the insert value can be changed.

Line 69 just does an INSERT on the test table with the values "1" and "One". This will fire both triggers.

Lines 70 shows the content of table test, after the previous INSERT, and line 71 shows the contents of the action_log table.

So, what do we see in the results?:

1. The value inserted in the test table was actually "0" and not "1"
2. The sequence column of the action_log table shows that the BEFORE actions of both triggers were executed first, then the FOR EACH ROW and last the both AFTER clauses.

In this case the triggers were executed in the creation order, but as stated in the manual we can't assume this. What is documented and can be assumed is that the order of execution of the actions is (it couldn't be any other way...):

1. First, all the BEFORE actions of all the triggers for the event
2. Then all the FOR EACH ROW actions of all the triggers for the event
3. In the end, all the AFTER actions of all the triggers for the event

We should also keep in mind that we can write triggered UDRs, and in these we can actually change the new values. Finally, we can also use the new operators to let the procedure code know which event are we running (INSERT/UPDATE/DELETE/SELECT).

Why would we want to use this feature? Well, for start, it can be handy in application conversions from other RDBMS where this is available and the existing application already uses it. Besides this, DBAs can use this to implement some triggered behavior in "blackbox applications". By this I mean applications where you don't have any influence on the developing phase. You can create your own triggers without interfering with existing ones. This can be used for auditing purposes or for some data extraction, based on changes made on the data fields.

So, in conclusion, the usefulness of this feature will depend largely on your environment requirements, but in some scenarios this can be a very valuable tool.

I think that if you look at the example above you'll see the most important aspects of this feature. So, take your time, and if any doubts remain don't hesitate to check the URL references to the manual and other sources. You can also leave a comment of course.

References:

• Information Center page of SQL Syntax Guide for CREATE PROCEDURE (REFERENCING and FOR clauses)
• Information Center page of SQL Syntax Guied for CREATE TRIGGER