CODEWITHSUNDEEP
oracle-databasetriggers
Paul W
Paul W

Reputation: 11373

Control order of database logon trigger execution in Oracle

We have two "AFTER LOGON ON DATABASE" triggers, each owned by different DBA-owned schemas and serving different purposes. Trigger A is consistently getting executed prior to trigger B. We need to reverse that and ensure that trigger B fires first.

According to the documentation, we supposedly could use "PRECEDES ..." syntax to control this, but when used with a database trigger it throws ORA-25025: cannot specify PRECEDES clause, so that's a dead-end.

Any ideas how to control which of these AFTER LOGON triggers fires first?

Does anybody know if dbms_ddl.set_trigger_firing_property can do this with some secret property value?

Upvotes: 0

Views: 446

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

PRECEDES is about editioning triggers. Check out FOLLOWS

SQL> create or replace
  2  trigger trg1
  3  after logon on scott.schema
  4  begin
  5      null;
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> create or replace
  2  trigger trg2
  3  after logon on scott.schema
  4  follows trg1
  5  begin
  6      null;
  7  end;
  8  /

Trigger created.

SQL>

or at database level

SQL> create or replace
  2  trigger trg1
  3  after logon on database
  4  begin
  5      null;
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> create or replace
  2  trigger trg2
  3  after logon on database
  4  follows trg1
  5  begin
  6      null;
  7  end;
  8  /

Trigger created.

Upvotes: 2

Related Questions