| 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 |