CODEWITHSUNDEEP
oracle-databasetriggers
lowLatency
lowLatency

Reputation: 5654

Are triggers fired during DDL statements?

Say you have a "on delete" trigger

1 What will happen when you fire a delete?

Trigger will be fired and trigger body will be executed

  1. What will happen when you fire a truncate?

    My guess - nothing will happen as truncate(DDL) and delete(DML) are totally different commands

One more question: Triggers are not fired on DDL statements, right?

Upvotes: 1

Views: 2915

Answers (2)

Ollie
Ollie

Reputation: 17538

You are correct with your assertions in your question as @Eggi says.

In addition to his answer and the details given in his link, TRUNCATE will reset the Highwater Mark in your table whereas DELETE will not.

If your table changes in size by a large amount then this could be very pertinent.

From AskTom:

The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain no data, during a full scan. This will impact the performance of a full scan¿especially if most of the blocks under the HWM are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a SELECT COUNT() from this table. Now, DELETE every row in it and you will find that the SELECT COUNT() takes just as long (or longer, if you need to clean out the block! Refer to the 'Block Cleanout' section of Chapter 9) to count 0 rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the HWM to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the HWM of a table back to 'zero' and will truncate the associated indexes on the table as well. If you plan on deleting every row in a table, TRUNCATE¿if it can be used¿would be the method of choice for this reason.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:492636200346818072

Hope it helps...

Upvotes: 2

Eggi
Eggi

Reputation: 1714

  1. Correct
  2. Trigger will not fire and you are right that no DML triggers should fire on DDL statements.

More information: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Upvotes: 1

Related Questions