Category: Oracle

The ALTER TRIGGER statement is used to enable a trigger. Syntax Parameters trigger_name: It specifies the name of the trigger that you want to enable. Oracle ENABLE Trigger Example This example will enable the trigger named “SUPPLIERS_T1” in the “SUPPLIERS” table. Oracle ENABLE ALL Triggers Example Syntax Example This example will enable all the triggers on…

The ALTER TRIGGER statement is used to disable a trigger. Syntax Parameters trigger_name: It specifies the name of the trigger that you want to disable. Oracle DISABLE Trigger Example This example will disable the trigger called “SUPPLIERS_T2” from the table “SUPPLIERS”. Oracle DISABLE ALL Triggers Example If there is more than one trigger in a table…

In Oracle, DROP TRIGGER statement is used to drop the trigger if you find that you need to remove it from the database. Syntax Parameters trigger_name: It specifies the name of the trigger that you want to drop. Oracle DROP Trigger Example It will drop the trigger name “SUPPLIERS_T1” from the table “SUPPLIERS”.

This statement specifies that Oracle will fire this trigger AFTER the INSERT/UPDATE or DELETE operation is executed. Syntax Parameters OR REPLACE: It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates you to change the trigger definition without using a DROP TRIGGER statement. trigger_name: It specifies the name of the trigger…

This statement specifies that Oracle will fire this trigger BEFORE the INSERT/UPDATE or DELETE operation is executed. Syntax Parameters OR REPLACE: It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates you to change the trigger definition without using a DROP TRIGGER statement. trigger_name: It specifies the name of the trigger…

In Oracle, you can define procedures that are implicitly executed when an INSERT, UPDATE or DELETE statement is issued against the associated table. These procedures are called database triggers. There are six CREATE TRIGGER statements according to their firing points. Firing Point: BEFORE Firing Point: AFTER

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statements like INSERT, UPDATE, DELETE or MERGE. Cursor is a mechanism which facilitates you to assign a name to a SELECT statement and manipulate the information within that SQL statement. How to declare cursor…

A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block. CREATE function in Oracle Syntax You must have define some…

A procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL. Create Procedure Syntax Following are the three types of procedures that must be defined to…

Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS sub-query. A semi-join returns one copy of each row in first table for which at least one match is found. Semi-joins are written using the EXISTS construct. Oracle Semi Join Example Let’s take two tables “departments” and “customer” Departments…