Database triggers are procedures that are fired/executed/triggered before or after data manipulation commands (DML). There are also “system triggers” that can be fired by other actions; such as logon, logoff, DDL statements within a schema, DDL statements within a database, server errors and others. These are not commonly used (especially not by analysts but DBAs) and thus I will focus more on triggers fired by DML operations.
There are three DML operations able to activate a trigger:
Some might argue that MERGE should be here on the list as well and they are right. Nonetheless, MERGE is only a combination of INSERTs and UPDATEs thus I won’t list it here.
Triggers might be pretty useful if you want to enforce business logic in data that cannot be implemented by constraints. You can historize data, create a complex security audit tracker or just replace invalid values before they are stored in the table.
Advantages and disadvantages of using triggers
There are several advantages but as well as some drawbacks. See the table below for more details.
|Prevent invalid transactions||No control over the trigger (for example when a procedure fails, the trigger is not fired)|
|Enforce complex security authorizations||No transparency (it might be easily overlooked what is happening on the background)|
|Enforce referential integrity across nodes in a distributed database||Performance (the more complex rules are implemented in triggers, the more time it will take to process the rows)|
|Enforce complex business rules|
|Generates a derived column values|
Basic commands for managing triggers
To create a trigger you can check the template below followed by a simple example.
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER -- just one of them INSERT|DELETE|UPDATE -- either one of them or you can combine them together by using OR (i.e. INSERT OR UPDATE) [REFERENCING OLD AS old_row NEW AS new_row] [FOR EACH ROW] BEGIN -- check specifically for INSERTing operations IF INSERTING THEN -- do something END IF; -- check specifically for UPDATing operations IF UPDATING THEN -- do something END IF; -- check specifically for DELETEing operations IF DELETING THEN -- do something END IF; EXCEPTION WHEN ... -- exception part END;
You can do different actions for different operations or you can define just one action without specifying for which operation it should be triggered. See the next example of a trigger.
-- audit trigger CREATE OR REPLACE TRIGGER audit_tbl BEFORE INSERT OR UPDATE ON my_table REFERENCING OLD AS orig NEW AS new FOR EACH ROW BEGIN -- generate an ID for the new record IF INSERTING THEN SELECT my_table_id_seq.NEXTVAL INTO :new.ID FROM DUAL; END IF; -- this part will generate a sysdate into to DTIME_UDPATED column SELECT sysdate INTO :new.dtime_updated FROM DUAL; -- this part will generate a username of the active user into the UPDATED_BY column SELECT SYS_CONTEXT ( 'USERENV', 'OS_USER' ) INTO :new.updated_by FROM DUAL; END;
Another example shows how you can prevent accidental deletion of records.
-- archivation of records in the employee table -- AFTER is choosen because we want to archive only those successfully updated / deleted CREATE OR REPLACE TRIGGER archive_emp AFTER UPDATE OR DELETE ON employee REFERENCING OLD AS orig NEW AS new FOR EACH ROW DECLARE v_oper_type CHAR(1); BEGIN IF UPDATING THEN v_oper_type := 'U'; IF DELETING THEN v_oper_type := 'D'; INSERT INTO employee_h VALUES (:orig.id_employee, :orig.name_employee, :orig.id_department, :orig.hire_date, sysdate, SYS_CONTEXT ( 'USERENV', 'OS_USER' ), v_oper_type); END;
At last, I will show you an example of a system trigger.
-- excel connection blocking CREATE OR REPLACE TRIGGER trg_block_excel AFTER LOGON ON DATABASE DECLARE prog_ sys.v_$session.program%TYPE; BEGIN SELECT UPPER(program) INTO prog_ FROM sys.v_$session WHERE 1=1 AND audsid = USERENV('SESSIONID') AND audsid != 0; IF prog_ LIKE '%EXCEL%' THEN RAISE_APPLICATION_ERROR(-20000, 'You are not allowed to connect to this database using Excel!'); END IF; END;
Now, as you learned how to create a trigger, here are some commands to drop it, disable it, or enable it.
-- drop a trigger DROP trigger_name; -- disable or enable a trigger ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE; -- here is the list of all triggers in the database with all details SELECT * FROM dba_triggers
To sum up, triggers might be very useful helpers with regards to enforcing some business rules, data protection or security rules implementation. On the other hand, be very careful about the performance. It might be a killer!