IDS Forum

Re: Triggers.

Posted By: RICARDO HENRIQUES
Date: Wednesday, 18 February 2015, at 8:26 a.m.

In Response To: Re: Triggers. (Ben Duncan)

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.

Messages In This Thread

• Triggers.
  Ben Duncan -- Monday, 16 February 2015, at 9:31 a.m.
  • Re: Triggers.
    RICARDO HENRIQUES -- Monday, 16 February 2015, at 12:58 p.m.
    • Re: Triggers.
      Ben Duncan -- Monday, 16 February 2015, at 4:23 p.m.
      • Re: Triggers.
        RICARDO HENRIQUES -- Tuesday, 17 February 2015, at 3:52 a.m.
        • Re: Triggers.
          Ben Duncan -- Tuesday, 17 February 2015, at 12:36 p.m.
          • Re: Triggers.
            RICARDO HENRIQUES -- Wednesday, 18 February 2015, at 8:26 a.m.
            • Re: Triggers.
              Ben Duncan -- Wednesday, 18 February 2015, at 7:56 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.