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
7 col1 INTEGER,
8 col2 CHAR(20)
9 ) LOCK MODE ROW;
11 CREATE TABLE action_log
13 trig_name CHAR(20),
14 trig_event CHAR(20),
15 trig_action CHAR(20),
16 trig_seq SERIAL
17 ) LOCK MODE ROW;
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'
29 WHEN DELETING THEN "DELETING"
30 WHEN UPDATING THEN "UPDATING"
31 WHEN INSERTING THEN "INSERTING"
32 WHEN SELECTING THEN "SELECTING"
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;
40 CREATE TRIGGER ti_1_test INSERT ON test
43 EXECUTE PROCEDURE trigger_proc('ti_1_test','BEFORE')
45 FOR EACH ROW
47 EXECUTE PROCEDURE trigger_proc('ti_1_test','FOR EACH ROW'),
48 EXECUTE PROCEDURE trigger_change() WITH TRIGGER REFERENCES
52 EXECUTE PROCEDURE trigger_proc('ti_1_test','AFTER')
55 CREATE TRIGGER ti_2_test INSERT ON test
58 EXECUTE PROCEDURE trigger_proc('ti_2_test','BEFORE')
60 FOR EACH ROW
62 EXECUTE PROCEDURE trigger_proc('ti_2_test','FOR EACH ROW')
66 EXECUTE PROCEDURE trigger_proc('ti_2_test','AFTER')
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
1 row(s) inserted.
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.
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:
- 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
- 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...
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?:
- The value inserted in the test table was actually "0" and not "1"
- 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.
- First, all the BEFORE actions of all the triggers for the event
- Then all the FOR EACH ROW actions of all the triggers for the event
- In the end, all the AFTER actions of all the triggers for the event
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.