• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql

CREATETRIGGERSPLSQL

BEFORE INSERT TRIGGER in Oracle PLSQL

December 12, 2012 by techhoneyadmin

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:

view source
print?
1CREATE TABLE new_employee
2(
3  employee_id    NUMBER
4  ,employee_name VARCHAR2(1000)
5  ,creation_date DATE
6  ,created_by    VARCHAR2(1000)
7);

If we query the ‘new_employee’ table as:

view source
print?
1SELECT *
2FROM 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:

view source
print?
1CREATE OR REPLACE TRIGGER new_employee_trigger
2BEFORE INSERT
3  ON new_employee
4FOR EACH ROW
5DECLARE
6  v_creator_name VARCHAR2(1000);
7BEGIN
8--Getting the name of the current logged in User
9  SELECT USER INTO v_creator_name
10  FROM dual;
11--setting system date in created_date column
12  :new.creation_date := sysdate;
13--setting the user name to created_by column
14  :new.created_by    := v_creator_name;
15END;

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:

view source
print?
1INSERT INTO new_employee
2VALUES
3(1, 'Emp ABCD',NULL,NULL);

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

view source
print?
1SELECT *
2FROM 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.


Filed Under: plsql Tagged With: Before insert trigger in oracle plsql, BeforeInsertTriggerPLSQL, CREATETRIGGERSPLSQL, how to create Before insert trigger in oracle plsql, what is Before insert trigger in oracle plsql

TRIGGERS in Oracle PLSQL

December 11, 2012 by techhoneyadmin

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:

  1. Insert Trigger
  2. Update Trigger
  3. 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:

  • Before Insert Trigger
  • After Insert Trigger

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:

  • Before Update Trigger
  • After Update Trigger

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:

  • Before Delete Trigger
  • After Delete Trigger

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:

view source
print?
1DROP 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:

view source
print?
1ALTER 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:

view source
print?
1ALTER 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:

view source
print?
1ALTER 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:

view source
print?
1ALTER TABLE employee DISABLE ALL TRIGGERS;

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


Filed Under: plsql Tagged With: CREATETRIGGERSPLSQL, drop enable and disable trigger in oracle plsql, How to create triggers in oracle plsql, types of triggers in oracle plsql

  • « Go to Previous Page
  • Page 1
  • Page 2

Copyright © 2025 · Parallax Pro on Genesis Framework · WordPress · Log in