How to make Data Base Trigger in Oracle?

What is Database Trigger

Database triggers are designed and applied on tables to execute an event in the database. When we perfume a transaction on table then trigger execute and perfume the specific task. Database trigger are same like stored procedures but stored procedures are call explicitly on the other hand database triggers are call implicitly in database

.

:Types of Database Triggers:

 There are two types of triggers.

• Statement-Level Triggers
• Row-Level Triggers

Statement-Level Trigger

:

Statement level trigger are executed once at a time. Statement level triggers can not be used to access the data that is going for inserting, updating or deleting because they do not have the access of keywords that are using normally to manipulating the data NEW and OLD. These two keywords are used in database trigger but not in statement level triggers. Statement level triggers are usually used for

• Like nobody login in database after5 pm.
• Manage the track of user login.
• To restrict the DML operation on any table.

Row-Level Triggers

:

A Row Level trigger fired every time for each row if we want to insert, update and delete data in table like if we performed DML operation on table and there are 20 rows that are effected then trigger will be executed 20 time on the other hand Statement-Level trigger fired one time. By default each trigger is Statement-Level trigger for making Row-Level trigger we use key word FOR EACH ROW in create trigger statement

.

Before and After Triggers

:

There are two types using of these triggers BEFORE and AFTER. Before trigger is normally used for check the validity of the data before the action is performed like deletion of data to check the data validity. There is no hard and fast rule to using before and after triggers both can use to creation the trigger

.

Sample code for Trigger

:

CREATE [OR REPLACE] TRIGGER (Trigger’s Name)

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF columns]}

 [OR {DELETE | INSERT |UPDATE [OF columns]}]… ON table [FOR EACH ROW [WHEN condition]] [REFERENCING [OLD AS old] [NEW AS new]]

PL/SQL Block

=========================

SQL> CREATE TABLE  EMP
2  (
3  EMP_CODE        NUMBER,
4  EMP_NAME        VARCHAR2(100),
5  EMP_SALARY      NUMBER,
6  USER_NAME       VARCHAR2(100),
7  DATE_TIME       DATE
8  );

Table created.

SQL> CREATE TABLE EMP_LOG AS SELECT * FROM EMP;
Table created.
SQL>

SQL> CREATE OR REPLACE TRIGGER TRG_EMP_DTL
2  BEFORE INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW
3  BEGIN
4  –>> FOR INSERTING
5  IF INSERTING THEN
6  INSERT INTO EMP_LOG
7  (
8  EMP_CODE,
9  EMP_NAME,
10  EMP_SALARY,
11  USER_NAME,
12  DATE_TIME
13  )
14  VALUES
15  (
16  :NEW.EMP_CODE,
17  :NEW.EMP_NAME,
18  :NEW.EMP_SALARY,
19  :NEW.USER_NAME,
20  :NEW.DATE_TIME
21  );
22  END IF;
23  –>> FOR UPDATING
24  IF  UPDATING  THEN
25  UPDATE EMP_LOG
26  SET
27  EMP_CODE       = :NEW.EMP_CODE,
28  EMP_NAME       = :NEW.EMP_NAME,
29  EMP_SALARY     = :NEW.EMP_SALARY,
30  USER_NAME      = :NEW.USER_NAME,
31  DATE_TIME      = :NEW.DATE_TIME
32  WHERE EMP_CODE = :OLD.EMP_CODE;
33  END IF;
34  –>> FOR DELETING
35  IF  DELETING THEN
36  DELETE FROM  EMP_LOG
37  WHERE EMP_CODE = :OLD.EMP_CODE;
38  END IF;
39  END;
40  /
Trigger created.