Triggers

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:

• INSERT
• DELETE
• UPDATE

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.

Why triggers?

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.

Advantages	Disadvantages
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
Logging
Auditing
Archiving
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!