CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Dzyann
Dzyann

Reputation: 5208

How to know if DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY was set to false for a Trigger?

Is there a way to know if DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY was set to false for a trigger?

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('"S1"','"MY_TRIGGER"',FALSE) ;

By default the property is set to TRUE, I need to query if the property was set to false, because when I re-create the trigger I'd need to set it again to false.

Upvotes: 0

Views: 1722

Answers (2)

Dzyann
Dzyann

Reputation: 5208

Justin's answer seems to work for Oracle 11, however those columns are not available in Oracle 10, which is what I am using.

In Oracle 10 the DBMS_DDL.IS_TRIGGER_FIRE_ONCE function returns the value of the flag for the trigger.

DECLARE

    isFiring BOOLEAN := false;
BEGIN
    isFiring := dbms_ddl.IS_TRIGGER_FIRE_ONCE('S1', 'MY_TRIGGER');
    IF isFiring = true THEN
        dbms_output.put_line('TRUE');
    ELSE
        dbms_output.put_line('FALSE');
    END IF;        
END;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

You'd want to look at the fire_once and apply_server_only columns in the all_triggers data dictionary view (or dba_triggers or user_triggers depending on your privileges and the specific set of triggers you're looking at).

Upvotes: 1

Related Questions