A DDL statement may fire a DML trigger

Maybe you know this, maybe you don't. Because it's not quite obvious it deserves a little attention. We all know about DML triggers. Remember? Yea, yea... the before/after insert/update/delete each row triggers. We use to think that the INSERT, UPDATE or DELETE statements fire the corresponding triggers (of course, if any are defined). That's true with one (as far as I know) important note: a DDL statement which adds a new column with a default value will also fire the UPDATE trigger.

For example, let's create a dummy table:

SQL> create table muc  (col1 integer primary key, modify_date timestamp);

Table created.

Then, the corresponding trigger:

SQL> create or replace trigger trg_muc_mod_dt before update on muc for each row
2  begin
3  :new.modify_date := systimestamp;
4  end;
5  /

Add some records:

SQL> insert into muc values (1, systimestamp);

1 row created.

SQL> insert into muc values (2, systimestamp);

1 row created.

SQL> commit;

We end up having:

SQL> select * from muc;

    COL1 MODIFY_DATE
---------- ------------------------------
       1 16-DEC-09 09.54.03.804223 PM
       2 16-DEC-09 09.54.41.815575 PM

Now, the moment of truth:

SQL> alter table muc add (active integer default '0');

Table altered.

SQL> select * from muc;

    COL1 MODIFY_DATE                        ACTIVE
---------- ------------------------------ ----------
       1 16-DEC-09 09.55.53.836113 PM            0
       2 16-DEC-09 09.55.53.840896 PM            0

Take a look at the MODIFY_DATE and see the new timestamp. The update trigger was invoked in response to our DDL statement. This is important to know. Think to a deposit table which has a column named LAST_UPATED and a trigger which updates it whenever something within a deposit changes. Now, suppose the business logic dictates that a new column must be added with a default value. You run the DDL statement to add that column and... suddenly, all information regarding when a particular deposit was last upated is lost. Ups. So, I should write down one hundred times: "Think twice before adding new columns with default values on a table with UPDATE triggers".