CREATETRIGGERSPLSQL

Oracle PLSQL BEFORE INSERT TRIGGER means that the trigger will get executed or triggered, just before an INSERT operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a BEFORE INSERT TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the trigger_name is the name given to trigger.

Some points to remember about PLSQL BEFORE INSERT TRIGGER are:

We cannot create a BEFORE INSERT TRIGGER on an Oracle PLSQL VIEW.

We can update :NEW values with BEFORE INSERT TRIGGER.

We cannot update the :OLD values using BEFORE INSERT TRIGGER.

Example of Oracle PLSQL BEFORE INSERT TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id    NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by    VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in the ‘new_employee’ table

Creating an Oracle PLSQL BEFORE INSERT TRIGGER on ‘new_employee’ table

Now let’s create a trigger on ‘new_employee’ table so that whenever we enter any employee record, the CREATION_DATE and CREATED_BY columns for that record gets filled automatically.

For this purpose we can create a PLSQL BEFORE INSERT TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE INSERT
  ON new_employee
FOR EACH ROW
DECLARE
  v_creator_name VARCHAR2(1000);
BEGIN
--Getting the name of the current logged in User
  SELECT USER INTO v_creator_name
  FROM dual;
--setting system date in created_date column
  :new.creation_date := sysdate;
--setting the user name to created_by column
  :new.created_by    := v_creator_name;
END;

Here we have created a PLSQL BEFORE INSERT TRIGGER named ‘new_employee_trigger’ which will update the CREATED_BY and CREATION_DATE columns in the ‘new_employee’ table.

Let’s see PLSQL BEFORE INSERT TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',NULL,NULL);

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/12/2012 5:34:49 PM	SCOTT

Here although we have passed NULL values for the CREATION_DATE and CREATED_BY columns, the PLSQL BEFORE INSERT TRIGGER (‘new_employee_trigger’) inserts the CREATED_BY and CREATION_DATE columns in the ‘new_employe’ table.

Please note that the values in CREATTION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

Oracle PLSQL TRIGGER is a set of statements that get executed or triggered as a side effect of modification of database.

Oracle PLSQL Triggers can be categorized as:

Insert Trigger
Update Trigger
Delete Trigger

1. Insert Trigger:- This type of plsql trigger gets executed as soon as an INSERT action is performed on a table.

Insert trigger can be of two types:

2. Update Trigger:- This type of plsql trigger gets executed as soon as an UPDATE action is performed on a table.

Update Trigger can be of 2 types:

3. Delete Trigger:- This type of plsql trigger gets executed as soon as a DELETE action is performed on a table.

Delete Trigger can be of 2 types:

Please click on the trigger types above to understand the detailed explanation of each trigger type with examples.

Drop an Oracle PLSQL trigger:

Oracle PLSQL allows up to drop a trigger which we have created previously

PLSQL Syntax to drop a trigger is:

DROP TRIGGER trigger_name;

Example to drop a PLSQL trigger:

DROP TRIGGER trigger_employee_update;

The above drop statement will drop the trigger named ‘trigger_employee_update’ from the data base.

Enable and Disable an Oracle PLSQL Trigger:

1. Enable a PLSQL Trigger

Oracle PLSQL allows us to enable the trigger that we have created and disabled previously using ALTER statement and ENABLE keyword.

Oracle PLSQL syntax to enable a trigger is:

ALTER TRIGGER trigger_name;

Example to enable a PLSQL trigger:

ALTER TRIGGER trigger_employee_update ENABLE;

The above drop statement will enable the trigger named ‘trigger_employee_update’.

2. Enable all PLSQL Triggers on a table

Oracle PLSQL allows us to enable all the triggers on a table at once using the ALTER statement with the ENABLE ALL TRIGGERS keyword.

Syntax to enable all PLSQL triggers on a table is:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

Example to enable all Oracle PLSQL triggers on a table:

ALTER TABLE employee ENABLE ALL TRIGGERS;

The above ALTER TABLE statement will enable all the triggers on ‘employee’ table.

3.Disable an Oracle PLSQL Trigger

We can disable a PLSQL Trigger that we have created earlier by using the ALTER statement with the DISABLE keyword.

Oracle PLSQL syntax to disable a trigger is:

ALTER TRIGGER trigger_name DISABLE;

Example to disable a PLSQL trigger in PLSQL:

ALTER TRIGGER trigger_employee_update DISABLE;

The above ALTER TRIGGER statement will disable the ‘trigger_employee_update’ trigger.

4. Disable all Oracle PLSQL Triggers on a table

Oracle PLSQL allows us to disable all the trigger on a table at once using the ALTER TABLE Statement with the DISABLE ALL TRIGGERS keyword.

Syntax to disable all plsql triggers on a table is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Example to disable all PLSQL triggers on a table:

ALTER TABLE employee DISABLE ALL TRIGGERS;

The above statement will disable all the triggers on ‘employee’ table.

BEFORE INSERT TRIGGER in Oracle PLSQL