getting rid of Insert trigger

Tryin开发者_开发百科g to explore solutions alternative to using insert triggers. Like API based ones and pros and cons with different approaches.

---

In an API approach you would create a procedure to perform both operations - something like:

package body emp_api is

    procedure insert_emp (...) is
    begin
        insert into emp (...) values (...);
        -- Insert that was previously in trigger
        insert into other_table (...) values (...);
    end;
end;

Then you force applications to use the API by giving them EXECUTE access to the api package but no INSERT/UPDATE/DELETE access to the tables.

---

If you want to guarantee that you'll have a record inserted into tableB when something inserts into tableA, then keep the trigger. You can disable if bulk loading into tableA and can guarantee you'll have the only process loading into that table during that time.

As soon as you remove the trigger, you have NO guarantees about inserts into tableB. Your only hope is that any and all programs that may insert into tableA (do you really know all of these?) adhere to the secondary insert into tableB. This is "data integrity via company policy", not data integrity enforced via Oracle.

This approach depends on how much you care about the state of the data in tableB I suppose.

I would NOT go the route of table apis (TAPIs), which now force any/all operations through some pl/sql api that handles the logic. These almost always tend to be slow and buggy in my experience.

---

In DDL You can disable a trigger with ALTER TRIGGER or ALTER TABLE.

ALTER TRIGGER triggername DISABLE;  -- disable a single trigger

ALTER TABLE tablename DISABLE ALL TRIGGERS; -- disable all triggers on a table

To do this at runtime, you would have to use dynamic SQL and the schema in which the procedure is running must own the table or otherwise have the necessary privileges.

EXECUTE IMMEDIATE 'ALTER TRIGGER tablename DISABLE ALL TRIGGERS';

For more info on enabling/disabling triggers, see http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/general004.htm