Triggers.
The poster asked when Informix fires trigger actions for INSERT/UPDATE/DELETE when AFTER isn't specified. The answer: a trigger must define at least one of BEFORE, FOR EACH ROW, or AFTER, and the clauses are independent with no implicit default. BEFORE and AFTER run once per triggering statement (even if no rows are affected), before and after the DML respectively; FOR EACH ROW runs per affected row, just after each row is processed but before values are written to the log/table. A worked example with audit tables demonstrated the ordering, and the questioner was satisfied.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
When AFTER is not defined, when does IDS fire the trigger functions for: Delete? Insert ? UPDATE ? Thanks ... -- Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212 "Never attribute to malice, that which can be adequately explained by stupidity" - Hanlon's Razor
I Ben, You will have to define, at least, one triggered action by using one of the keywords BEFORE, FOR EACH ROW, or AFTER. From the docs ( http://tinyurl.com/ppfxjao ): - The BEFORE actions are executed once for each triggering event, before the database server performs the triggering DML operation. - The AFTER actions are also executed once for each triggering DML event, after the operation on the table is complete, in the context of the triggering statement. - The FOR EACH ROW actions are executed for each row that is inserted, updated, deleted or selected in the DML operation, after the DML operation is executed on each row, but before the database server writes the values into the log and into the table. Best regards.
Thanks Ricardo. So the FOR EACH, is neither BEFORE or AFTER, but during - when there exists 'old and new' correct? On 02/16/2015 11:59 AM, RICARDO HENRIQUES wrote: > I Ben, > > You will have to define, at least, one triggered action by using one of the > keywords BEFORE, FOR EACH ROW, or AFTER. > >>From the docs ( http://tinyurl.com/ppfxjao ): > - The BEFORE actions are executed once for each triggering event, before the > database server performs the triggering DML operation. > - The AFTER actions are also executed once for each triggering DML event, > after the operation on the table is complete, in the context of the triggering > statement. > - The FOR EACH ROW actions are executed for each row that is inserted, > updated, deleted or selected in the DML operation, after the DML operation is > executed on each row, but before the database server writes the values into > the log and into the table. > > Best regards. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212 "Never attribute to malice, that which can be adequately explained by stupidity" - Hanlon's Razor
Yes, Ben. The FOR EACH ROW is executed after a row is processed, hence there has to be a new (INSERT/UPDATE) and/or old (DELETE/UPDATE). It executes row by row. The BEFORE and AFTER actions always execute once, regardless if rows are processed or not by the triggering statement.
Thanks .. Now for the FINAL question : If FOR each row, does not have a defined BEFORE or AFTER - what is the default ? On 02/17/2015 02:53 AM, RICARDO HENRIQUES wrote: > Yes, Ben. > > The FOR EACH ROW is executed after a row is processed, hence there has to be a > new (INSERT/UPDATE) and/or old (DELETE/UPDATE). It executes row by row. > > The BEFORE and AFTER actions always execute once, regardless if rows are > processed or not by the triggering statement. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212 "Never attribute to malice, that which can be adequately explained by stupidity" - Hanlon's Razor
Hi Ben,
Just to be clear, the actions clause BEFORE, FOR EACH ROW and AFTER are
independent.
When you create a trigger you must specify at least one, but if you specify
more than one they will not affect how the other ones execute. If you specify
more than one any BEFORE action list must be specified first, and any AFTER
action list must be specified last.
As said before:
BEFORE actions are executed only once for each triggering event and before the
DML, regardless if any row was affected by it;
FOR EACH ROW actions are executed for each row that is
inserted/deleted/updated by the DML;
AFTER actions are executed only once for each triggering event and after the
DML, regardless if any row was affected by it;
If you want the exact time of execution of the FOR EACH ROW actions you should
consider it as after the row is processed.
For example, let us create the next tables and trigger and insert some data:
infx1210@infxsrv:informix-> cat test_case.sql
CREATE TABLE tab1(
col1 INT
);
CREATE TABLE tab1_bck(
old_col1 INT
);
CREATE TABLE tab1_aud(
col1 INT,
col2 CHAR(1)
);
CREATE TRIGGER tu_tab1 UPDATE ON tab1
BEFORE
(INSERT INTO tab1_aud VALUES ((SELECT COUNT(*) FROM tab1_bck), 'B'))
FOR EACH ROW
(INSERT INTO tab1_bck SELECT * FROM tab1)
AFTER
(INSERT INTO tab1_aud VALUES ((SELECT COUNT(*) FROM tab1_bck), 'A'));
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1);infx1210@infxsrv:informix-> dbaccess db test_case.sql
Database selected.
Table created.
Table created.
Table created.
Trigger created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
Database closed.
infx1210@infxsrv:informix->
Let us do an update that will not affect any row:
infx1210@infxsrv:informix-> dbaccess db -
Database selected.
> UPDATE tab1 SET col1 = 2 WHERE col1 = 3;
0 row(s) updated.
> SELECT * FROM tab1_aud;
col1 col2
0 B
0 A
2 row(s) retrieved.
> SELECT * FROM tab1_bck;
old_col1
No rows found.
>
We can see that the BEFORE and AFTER trigger executed although no rows were
processed.
Now , let us do an update that will affect all rows:
> UPDATE tab1 SET col1 = 2 WHERE col1 = 1;
3 row(s) updated.
> SELECT * FROM tab1_aud;
col1 col2
0 B
0 A
0 B
9 A
4 row(s) retrieved.
> SELECT * FROM tab1_bck;
old_col1
2
1
1
2
2
1
2
2
2
9 row(s) retrieved.
>
Database closed.
infx1210@infxsrv:informix->
Again, the BEFORE and AFTER trigger executed and by the count on the tab1_bck
we can observe that they executed before and after, respectably, the
triggering event executed.
As for the FOR EACH ROW we can see that for each row updated he did a copy of
the table as it was after the change.
Hope that was clear, but if you still in doubt fire away.
Keen regards.
GREAT !! The explanation i was looking for U DA MAN !!
Ben
On 02/18/2015 07:27 AM, RICARDO HENRIQUES wrote:
> Hi Ben,
>
> Just to be clear, the actions clause BEFORE, FOR EACH ROW and AFTER are
> independent.
>
> When you create a trigger you must specify at least one, but if you specify
> more than one they will not affect how the other ones execute. If you specify
> more than one any BEFORE action list must be specified first, and any AFTER
> action list must be specified last.
>
> As said before:
> BEFORE actions are executed only once for each triggering event and before
the
> DML, regardless if any row was affected by it;
> FOR EACH ROW actions are executed for each row that is
> inserted/deleted/updated by the DML;
> AFTER actions are executed only once for each triggering event and after the
> DML, regardless if any row was affected by it;
>
> If you want the exact time of execution of the FOR EACH ROW actions you
should
> consider it as after the row is processed.
>
> For example, let us create the next tables and trigger and insert some data:
> infx1210@infxsrv:informix-> cat test_case.sql
> CREATE TABLE tab1(>
> col1 INT
> );
>
> CREATE TABLE tab1_bck(>
> old_col1 INT
> );
>
> CREATE TABLE tab1_aud(>
> col1 INT,
>
> col2 CHAR(1)
> );
>
> CREATE TRIGGER tu_tab1 UPDATE ON tab1>
> BEFORE
>
> (INSERT INTO tab1_aud VALUES ((SELECT COUNT(*) FROM tab1_bck), 'B'))
>
> FOR EACH ROW
>
> (INSERT INTO tab1_bck SELECT * FROM tab1)
>
> AFTER
>
> (INSERT INTO tab1_aud VALUES ((SELECT COUNT(*) FROM tab1_bck), 'A'));
>
> INSERT INTO tab1 VALUES (1);
> INSERT INTO tab1 VALUES (1);
> INSERT INTO tab1 VALUES (1);> infx1210@infxsrv:informix-> dbaccess db test_case.sql
>
> Database selected.
>
> Table created.
>
> Table created.
>
> Table created.
>
> Trigger created.
>
> 1 row(s) inserted.
>
> 1 row(s) inserted.
>
> 1 row(s) inserted.
>
> Database closed.
>
> infx1210@infxsrv:informix->
>
> Let us do an update that will not affect any row:
> infx1210@infxsrv:informix-> dbaccess db -
>
> Database selected.
>
>> UPDATE tab1 SET col1 = 2 WHERE col1 = 3;>
> 0 row(s) updated.
>
>> SELECT * FROM tab1_aud;>
> col1 col2
>
> 0 B
>
> 0 A
>
> 2 row(s) retrieved.
>
>> SELECT * FROM tab1_bck;>
> old_col1
>
> No rows found.
>
>>
>
> We can see that the BEFORE and AFTER trigger executed although no rows were
> processed.
>
> Now , let us do an update that will affect all rows:
>> UPDATE tab1 SET col1 = 2 WHERE col1 = 1;>
> 3 row(s) updated.
>
>> SELECT * FROM tab1_aud;>
> col1 col2
>
> 0 B
>
> 0 A
>
> 0 B
>
> 9 A
>
> 4 row(s) retrieved.
>
>> SELECT * FROM tab1_bck;>
> old_col1
>
> 2
>
> 1
>
> 1
>
> 2
>
> 2
>
> 1
>
> 2
>
> 2
>
> 2
>
> 9 row(s) retrieved.
>
>>
>
> Database closed.
>
> infx1210@infxsrv:informix->
>
> Again, the BEFORE and AFTER trigger executed and by the count on the tab1_bck
> we can observe that they executed before and after, respectably, the
> triggering event executed.
>
> As for the FOR EACH ROW we can see that for each row updated he did a copy of
> the table as it was after the change.
>
> Hope that was clear, but if you still in doubt fire away.
>
> Keen regards.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212
"Never attribute to malice, that which can be adequately explained by
stupidity"
- Hanlon's Razor