The “Control Freak Trigger” Design Pattern

Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional stuff.

CREATE OR REPLACE PACKAGE my_package AS 
    PROCEDURE insert_t1 (...);
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package AS 
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
    END insert_t1;
END my_package;
/

But what will happen if someone (perhaps even myself in one year from now) will insert records into T1 from another place (in another procedure or directly via an INSERT statement) and will not know (or maybe forget) about the mandatory additional stuff?
The best thing would be to make this procedure the one and only way to insert records into T1. But how to do it?
I like doing it in a way that I call “the control freak trigger”, using a combination of a global variable and a trigger (but this trigger doesn’t do actual stuff, it is only a gatekeeper).

I’m adding a Boolean global variable and initialize it to FALSE. I change it to TRUE at the beginning of the insert_t1 procedure, and make sure to set it back to FALSE when leaving the procedure.

CREATE OR REPLACE PACKAGE BODY my_package AS 
    g_is_insert_into_t1_allowed BOOLEAN := FALSE; 
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        g_is_insert_into_t1_allowed := TRUE; 
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
        g_is_insert_into_t1_allowed := FALSE; 
    EXCEPTION 
        WHEN OTHERS THEN 
            g_is_insert_into_t1_allowed := FALSE; 
            RAISE; 
    END insert_t1;
END my_package;
/

Now I’m adding a function – a public function – to get the current value of the global variable:

CREATE OR REPLACE PACKAGE my_package AS 
    FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN;  
    PROCEDURE insert_t1 (...);
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package AS 
    g_is_insert_into_t1_allowed BOOLEAN := FALSE;
    FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN IS 
    BEGIN 
        RETURN g_is_insert_into_t1_allowed; 
    END is_insert_into_t1_allowed;  
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        g_is_insert_into_t1_allowed := TRUE;
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
        g_is_insert_into_t1_allowed := FALSE;
    EXCEPTION 
        WHEN OTHERS THEN 
            g_is_insert_into_t1_allowed := FALSE;
            RAISE;
    END insert_t1;
END my_package;
/

And as the final step I’m creating a statement level trigger – BEFORE INSERT ON T1; a trigger that doesn’t change anything, it just checks that the global variable is currently set to TRUE.

CREATE OR REPLACE TRIGGER t1_trig
    BEFORE INSERT ON t1
BEGIN 
    IF NOT my_package.is_insert_into_t1_allowed THEN 
        raise_application_error(-20000,
          'INSERTs into T1 are allowed only via my_package.insert_t1');
    END IF;
END t1_trig;
/

So if I’ll try to insert records into T1 not via the procedure the trigger will not allow it, it will raise an exception saying “INSERTs into T1 are allowed only via my_package.insert_t1”.
Obviously this pattern can be implemented for other operations, not only for INSERT.

One Comment

  1. brosh sikso

    Excellent!
    Looks simple.
    I will implement it on my work for sure.

    Thanks for sharing it.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>