Create Triggers that are Invoked by DML Operations

What are Triggers?

Triggers are similar to stored procedures. A trigger is a PL/SQL block that is stored in the database. A trigger is automatically executes by the Oracle database when predefined conditions occur. Basically trigger is fired or executed to a specified event. Triggers can be assigned to tables, view, schema or database.

Trigger have two level, Statement level triggers and Row level triggers. Statement level triggers is default and use the FOR EACH ROW clause when creating row level trigger. We can write triggers that to be executed in response one of the following operations occurs in the database:

–> A database manipulation (DML) statement (DELETE, INSERT, UPDATE).
–> A database definition (DDL) statement (CREATE, ALTER, DROP).
–> A database operation such as SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN.
Trigger Types:
===========

–> Simple DML triggers (INSER, UPDATE [OF column], DELETE are the possible events for DML)

• BEFORE
• AFTER
• INSTEAD OF

–> Compound triggers

–> Non-DML triggers

• DDL event triggers
• Database event triggers

Syntaxt of Creating DML Trigger:
=========================

CREATE [OR REPLACE] TRIGGER trigger_name
timing — when to fire the trigger
event1[OR event2 OR event3]
ON object_name
[REFERENCING OLD as old / NEW as new]
FOR EACH ROW — default is statement level trigger
WHEN (condition)
DECLARE
BEGIN
——-trigger body —- executable statements
EXCEPTION
END trigger_name;

**timing = BEFORE / AFTER / INSTEAD OF
**event = INSERT / DELETE / UPDATE / UPDATE OF column_list

Now I am showing an example of DML statement trigger:

CREATE OR REPLACE TRIGGER table_secure
BEFORE INSERT ON employees
    BEGIN
            IF (TO_CHAR (SYSDATE, 'DY') IN ('TUE', 'WED')) OR
               (TO_CHAR (SYSDATE, 'HH:MI') NOT BETWEEN '08:00' AND '05:00') THEN
              RAISE_APPLICATION_ERROR (-20500, 'You need normal business hours to change table');
            END IF;
    END;
/

I have created a trigger to secure my EMPLOYEES table data. My trigger name is table_secure. This trigger will prevent the INSERT operation from succeeding if the business condition is violated. If a user want to insert a row into the EMPLOYEES table on Tuesday OR Wednesday or time not between 8 am to 5 pm then user will see an error message. I have used an error message manually within the RAISE_APPLICATION_ERROR.

Now I am INSERTING below data to test my trigger table_secure:

INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)
	       VALUES(420, 'Jordan', 'EMJORDAN', SYSDATE, 'IT_PROG', 50000);

After execute the above query, I am getting the following error messages:

INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)
            *
ERROR at line 1:
ORA-20500: You need normal business hours to change table
ORA-06512: at "HR.TABLE_SECURE", line 4
ORA-04088: error during execution of trigger 'HR.TABLE_SECURE'

SO, I need normal business hour to insert this data into the table.

–> Example of row level trigger:

CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
   IF NOT :NEW.salary > 15000 THEN
           RAISE_APPLICATION_ERROR(-20300, 'Employee salary is less then $15000.');
   END IF;
END;
/

My above trigger is created to restrict salary for each row. I have given a condition to INSERT or UPDATE for salary. If new salary is greater than 15000 then it will be UPDATE or INSERTED otherwise oracle database will show an error message.

Now I am using below data to test my trigger.

UPDATE employees
SET salary = 14500
WHERE last_name = 'Ozer';

After executing the above query  , I am getting the following exception error messages :

UPDATE employees
       *
ERROR at line 1:
ORA-20300: Employee salary is less then $15000.
ORA-06512: at "HR.RESTRICT_SALARY", line 3
ORA-04088: error during execution of trigger 'HR.RESTRICT_SALARY'

So,we need salary amount more then 15000 for UPDATE data.