Trigger in Oracle

 

What is Trigger in Oracle:

Trigger in Oracle is just like hidden assistance which acts or works on specific tasks automatically whenever something happens in database. It’s like automated response to a particular event.

For example,

Suppose you have library database and you want to keep tracks on when books borrowed. We can use trigger to automatically track record the information in a log whenever someone checks out the book.

 

In Oracle database trigger is a stored PL/SQL program unit associated with a specific table or view.  Triggers are automatically executed when specific event is occurred in the database. The events of data or database are INSERT, UPDATE OR DELETE operations or specific system operation such as startup or shutdown.

Uses of Trigger:

n  Triggers are used to automate the tasks,

n  To enforce data integrity

n  To maintain data validity

 

 

Types of Triggers:

1.     DML Triggers:

These triggers include:

·        BEFORE INSERT:

The BEFORE INSERT trigger fires before the insert event or operation happen

·        BEFORE UPDATE:

This trigger fires before the update event or operation

·        BEFORE DELETE:

This trigger fires before the delete operation

·        AFTER INSERT:

This trigger fires after an insertion operation

·        AFTER UPDATE:

This trigger fires after the update operation

·        AFTER DELETE:

This trigger fires after the delete operation.

 

 

 

Syntax of Trigger: (DML TRIGGER)
 

CREATE OR REPLACE TRIGGER Trigger_Name

[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON Table_Name

[FOR EACH ROW [ WHEN(Condition)]]

DECLARE

            --Variable declaration

          BEGIN

          --Trigger logic, PLSQL code

          END;

CREATE OR REPLACE TRIGGER:

These keywords are used to create new trigger or replace existing or modify existing trigger.

Trigger_Name:

Trigger_Name is the name of trigger given by user.

BEFORE/ AFTER:

Specify the time of trigger that will be fired for DML operation. Before the DML operation or After the DML operation

INSERT/UPDATE/DELETE:

Mention DML operation to fire the created trigger

Table_Name:

Specify the name of table on which the trigger will act

FOR EACH ROW:

This clause is used to specify that the trigger operates at row level.

WHEN Condition:

You can use condition to specify that when the trigger fires.

DECLARE Variables:

The section is optional used to declare something in your code for trigger logic.

BEGIN:

PLSQL block starts from BEGIN where Trigger logic is written in this block.

 

For Example:

--After Insert Trigger

--Triger is created on Books table.

--Time is After

-- DML operation is Insert

--So Trigger will be fired After Inserting New rows into Books table

--The newly inserted referenced row will be inserted automatically into books_Details Table with Book_id and fixed values 'Book collor Black and Weight '1Kg'

 

CREATE OR REPLACE TRIGGER After_Insert_books

AFTER INSERT ON books

FOR EACH ROW

BEGIN

INSERT INTO Books_Details

VALUES(:New.book_id,'Black','1kg');

End;

/

 

 

-- Whenever you will insert new rows in to books table the new book_id will automatically will insert into the books_details table with the fixed values 'Black' and '1Kg'.

-- You just select the Books_Details Table to check the trigger is fiered or not After inserting the new rows into the Books table.

After_Insert_books this trigger will execute automatically whenever new insertion operation is occurring on Books Table.

The: New method is used to represent the new values of the row is being inserted or updated in the triggering table.

:New.Book_id is used to  access the value of Book_id column from the newly inserted row in the  Books table. : New is reference to the new row being inserted.

You just select the Books_Details table to check trigger is fired or not after inserting the new row in the triggering table i.e. Books table.

Important Note:

When you create the trigger that references the table and this referenced table must already exist in the database. The trigger does not create table for us. If we do not have referenced table, then first create the table and then create the trigger.

 In my database I have Books Table and Books_details table. Book_Id is the common column from both the tables.

To check the changes after the Insert Trigger Fire on Books Table,

Inserted new row in Books Table and then checked Books_details Table to check new row added into Books_details or not.

So Initialy in My Books_Details table following rows are present,

SQL> select * from books_details;

 

   BOOK_ID BOOK_COLOR BOOK_WEIGHT

---------- ---------- ---------------

       101 Red        100gm

       102 Red        100gm

       103 Red        100gm

Then I have inserted one new row in Books Table

SQL> insert into books

2values(117,'computer','program',200,'programming','Dr. Manisha','2nd','vision',12,2000);

1 row created.

And then finnaly selected Books_Details table to check trigger fiered or not.

SQL> select * from books_details;

 

   BOOK_ID BOOK_COLOR BOOK_WEIGHT

---------- ---------- ---------------

       101 Red        100gm

       102 Red        100gm

       103 Red        100gm

       117 Black      1kg