CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Kirti Waghmare
Kirti Waghmare

Reputation: 19

Update triggers

If I have a statement like below.

Update table emp set sal=10 where 1=2

And I have applied all the four triggers:

So which triggers will be fired?

Thanks in advance!!

Upvotes: 1

Views: 168

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

which triggers will be fired?

All of the relevant triggers will fire, unless one throws an exception and terminates the update. Your statement will update zero rows (because of the 1=2 filter). The statement still executes, so the statement-level triggers will fire; but as no rows are affected, the row-level triggers will not fire.

From the documentation:

A simple DML trigger fires at exactly one of these timing points:

  • Before the triggering statement runs ...
  • After the triggering statement runs ...
  • Before each row that the triggering statement affects ...
  • After each row that the triggering statement affects ...

Notice the "each row that the triggering statement affects" part for the row-level triggers. If the triggering statement doesn't affect any rows, they will not fire.

The order that they fire is also important, and that is shown in the documentation too:

If two or more triggers with different timing points are defined for the same statement on the same table, then they fire in this order:

  1. All BEFORE STATEMENT triggers
  2. All BEFORE EACH ROW triggers
  3. All AFTER EACH ROW triggers
  4. All AFTER STATEMENT triggers

You can see that in action, using a modified version of @Littlefoot's code, with updates of zero rows, a single row and then multiple rows.

There is further advice in the documentation that goes a bit beyond your specific question, but which you might find relevant or useful at some point:

If it is practical, replace the set of individual triggers with different timing points with a single compound trigger that explicitly codes the actions in the order you intend. For information about compound triggers, see "Compound DML Triggers".

If you are creating two or more triggers with the same timing point, and the order in which they fire is important, then you can control their firing order using the FOLLOWS and PRECEDES clauses (see "FOLLOWS | PRECEDES").

Upvotes: 2

Rashid Ansari
Rashid Ansari

Reputation: 7

You can use sp_settriggerorder to define the order of each trigger on a table. SQL Server triggers - order of execution

Upvotes: -1

Littlefoot
Littlefoot

Reputation: 142720

Why don't you test it?

SQL> create table test as select empno, sal from emp where deptno = 10;

Table created.

SQL> create or replace trigger trg_bu_r
  2    before update on test
  3    for each row
  4  begin
  5    dbms_output.put_line('Before row');
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trg_au_r
  2    after update on test
  3    for each row
  4  begin
  5    dbms_output.put_line('After row');
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trg_bu_s
  2    before update on test
  3  begin
  4    dbms_output.put_line('Before statement');
  5  end;
  6  /

Trigger created.

SQL> create or replace trigger trg_au_s
  2    after update on test
  3  begin
  4    dbms_output.put_line('After statement');
  5  end;
  6  /

Trigger created.

SQL> set serveroutput on
SQL> update test set sal = 10 where 1 = 2;
Before statement
After statement

0 rows updated.

SQL>

Upvotes: 2

Related Questions