Create Triggers that are invoked by DML operations

Posted by tagicon_admin | August 4, 2015 | in : PL/SQL

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:

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:

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

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

 

–> Example of row level trigger:

 

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.

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

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

Related Posts

Leave a Reply