Debian Codegraph @

doc/sql.extensions/README.universal_triggers

raw
1 ---------------------------
2 Universal triggers (FB 1.5)
3 ---------------------------
4
5 Function:
6 Allows triggers that handle multiple row operations.
7
8 Author:
9 Dmitry Yemanov <[email protected]>
10
11 Syntax rules:
12 CREATE TRIGGER name FOR table
13 [ACTIVE | INACTIVE]
14 {BEFORE | AFTER} <multiple_action>
15 [POSITION number]
16 AS trigger_body
17
18 <multiple_action> ::= <single_action> [OR <single_action> [OR <single_action>]]
19 <single_action> ::= {INSERT | UPDATE | DELETE}
20
21 Example(s):
22 1. CREATE TRIGGER TRIGGER1 FOR TABLE1 BEFORE INSERT OR UPDATE AS ...;
23 2. CREATE TRIGGER TRIGGER2 FOR TABLE2 AFTER INSERT OR UPDATE OR DELETE AS ...;
24
25 ODS:
26 Encoding of field RDB$TRIGGER_TYPE (relation RDB$TRIGGERS) has been extended
27 to allow complex trigger actions. Coding scheme is shown below (in C syntax):
28
29 // trigger type prefixes
30 #define TRIGGER_BEFORE 0
31 #define TRIGGER_AFTER 1
32
33 // trigger type suffixes
34 #define TRIGGER_INSERT 1
35 #define TRIGGER_UPDATE 2
36 #define TRIGGER_DELETE 3
37
38 // that's how trigger action types are encoded
39 /*
40 bit 0 = TRIGGER_BEFORE/TRIGGER_AFTER flag,
41 bits 1-2 = TRIGGER_INSERT/TRIGGER_UPDATE/TRIGGER_DELETE (slot #1),
42 bits 3-4 = TRIGGER_INSERT/TRIGGER_UPDATE/TRIGGER_DELETE (slot #2),
43 bits 5-6 = TRIGGER_INSERT/TRIGGER_UPDATE/TRIGGER_DELETE (slot #3),
44 and finally the above calculated value is decremented
45
46 example #1:
47 TRIGGER_AFTER_DELETE =
48 = ((TRIGGER_DELETE << 1) | TRIGGER_AFTER) - 1 =
49 = ((3 << 1) | 1) - 1 =
50 = 0x00000110 (6)
51
52 example #2:
53 TRIGGER_BEFORE_INSERT_UPDATE =
54 = ((TRIGGER_UPDATE << 3) | (TRIGGER_INSERT << 1) | TRIGGER_BEFORE) - 1 =
55 = ((2 << 3) | (1 << 1) | 0) - 1 =
56 = 0x00010001 (17)
57
58 example #3:
59 TRIGGER_AFTER_UPDATE_DELETE_INSERT =
60 = ((TRIGGER_INSERT << 5) | (TRIGGER_DELETE << 3) | (TRIGGER_UPDATE << 1) | TRIGGER_AFTER) - 1 =
61 = ((1 << 5) | (3 << 3) | (2 << 1) | 1) - 1 =
62 = 0x00111100 (60)
63 */
64
65 Note(s):
66 1. One-action triggers are fully compatible at ODS level with FB 1.0.
67 2. RDB$TRIGGER_TYPE encoding is order-dependant, i.e.
68 BEFORE INSERT OR UPDATE and BEFORE UPDATE OR INSERT will be coded differently,
69 although they have the same semantics and will be executed exactly the same way.
70 3. In multiple-action triggers both OLD and NEW contexts are available. If the
71 trigger invocation forbids one of them (e.g. OLD context for INSERT operation),
72 then all fields of that context will evaluate to NULL. If you assign to
73 improper context, runtime exception will be thrown.
74 4. You may use new context variables INSERTING/UPDATING/DELETING to check the
75 operation type at runtime.
76
77 See also:
78 README.context_variables