Skip to main content

Compund Trigger

 

Compound Trigger

 

 

In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points:

1.   Before the firing statement

2.   Before each row that the firing statement affects

3.   After each row that the firing statement affects

4.   After the firing statement

 

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.

 

CREATE OR REPLACE TRIGGER compound_trigger_name

FOR [INSERT|DELETE]UPDATE [OF column] ON table

COMPOUND TRIGGER

   -- Declarative Section (optional)

   -- Variables declared here have firing-statement duration.

    

     --Executed before DML statement

     BEFORE STATEMENT IS

     BEGIN

       NULL;

     END BEFORE STATEMENT;

  

     --Executed before each row change- :NEW, :OLD are available

     BEFORE EACH ROW IS

     BEGIN

       NULL;

     END BEFORE EACH ROW;

  

     --Executed aftereach row change- :NEW, :OLD are available

     AFTER EACH ROW IS

     BEGIN

       NULL;

     END AFTER EACH ROW;

  

     --Executed after DML statement

     AFTER STATEMENT IS

     BEGIN

       NULL;

     END AFTER STATEMENT;

 

END compound_trigger_name;

 

 

 

 

Comments

Ankit Gupta said…
Just a single blog cleared all my doubts regarding compound trigger...That's nice
July 14, 2021 at 11:02 PM
Post a Comment

Popular posts from this blog

Oracle Merge Statement Delta Detection

The MERGE Syntax by Ajay Nerumati Delta Detection in Oracle SQL Posted on  8. October 2016 Delta detection is a common task in every Data Warehouse. It compares new data from a source system with the last versions in the Data Warehouse to find out whether a new version has to be created. There are several ways to implement this in Oracle. Your source system delivers a full extraction every night, and you have to load only the changed rows into your Core Data Warehouse? You receive incremental loads from another source system every few minutes, but only a few columns are loaded into the Data Warehouse. In all these situations, you need a delta detection mechanism to identify the rows that have to be inserted or updated in your Data Warehouse tables. In this blog post, I want to show different methods in Oracle SQL that provide the subset of rows of a source table that were changed since the last load. All these methods are set-based, i.e. they can be executed in one SQL sta...
199 comments
Read more

FOREIGN KEY

Pre-requisits : General understanding of integrity constraints. Constrains are used to prevent invalid data into the table. Integrity constrains are business rules enforced on the data in a table. The enforced rules trigger whenever a row is inserted, updated, or deleted from that table and prevent data that does not meet the set rules. FOREIGN KEY :  Establishes and enforces a relationship between two tables or to the same table itself. The foreign key or referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.  In the example below   Emp_child  table's deptno   has been defined as the foreign key ; it references the deptno column of the Dept_parent table. Implementation: Step 1 Create parent table DEPT_PARENT as follows: Create table DEPT_PARENT ( Deptno n...
79 comments
Read more

RDBMS MINI PROJECT

  Capgemini class room training.   RDBMS MINI PROJECT ( SPRINT ) LIBRARY MANAGEMENT SYSTEM   Table of Contents Serial No. Topic Name Content Page No. 1.   Introduction 1.1 Setup checklist for mini project 3     1.2 Instructions 3 2.   Problem statement   2.1 Objective 4     2.2 Abstract of the project 4     2.3 Functional components of the project 4     2.4 Technology used 5 3.   Implementation in RDBMS LOT 3.1 Guidelines on the functionality to be built 6 4.   Evaluation 4.1 Evaluation 7   ...
1 comment
Read more