Triggers uses and Benefits in PL/SQL #DOTC_Mdb

Triggers uses and benefits in PL/SQL.

 

In Previous Blogs we gone through about PL/SQL blocks like Procedures, Cursors and Stored Function, Today I will give you overview about Triggers in PL/SQL.

A trigger is a pl/sql block structure or a named block which automatically fires when DML statements like Insert, Delete and Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Trigger is of two Types:

1.     Statement Level Trigger: In Statement level trigger an event is triggered for each sql statement executed. Statement level triggers executes only once for each single transaction.

 

2.     Row Level Trigger: An event is triggered for each row updated, deleted or inserted. Row level triggers executes once for each and every row in the transaction.

 

We will discuss both the trigger later, first we need to go to understand the syntax of Trigger in PL/SQL block with one example then we will go through both the triggers.

Syntax:

CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF }

 {INSERT [OR] | UPDATE [OR] | DELETE}

 [OF col_name]

 ON table_name

 [REFERENCING OLD AS o NEW AS n]

 [FOR EACH ROW]

 WHEN (condition) 

 BEGIN

   --- sql statements 

 END;

 

Let’s understand the syntax step by step:

 

CREATE [OR REPLACE ] TRIGGER trigger name - This  creates a trigger with the given name which we will give or overwrites an existing trigger with the same name.

BEFORE | AFTER | INSTEAD OF - This statements indicates at what time should trigger get fired. I.e. for example: before or after updating a table.  INSTEAD OF Clause is only used in view, we cannot use before and after clause trigger in a view.

INSERT [OR] | UPDATE [OR] | DELETE - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.

OF col_name - This clause is used when you want to trigger an event only when a specific column is updated , it’s mainly used with update triggers

 ON table_name - This clause mainly identifies the name of the table or view to which the trigger is associated.

REFERENCING OLD AS o NEW AS n - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name.

 

 FOR EACH ROW - This clause is used to determine whether a trigger must fire when each row gets affected (i.e. a Row Level Trigger) or just once when the entire sql statement is executed (i.e. statement level Trigger).

WHEN (condition) - This is Valid only for Row level trigger as this Trigger fired only for rows if it satisfied the specified Condition.

 

Let’s take one example to see how Trigger works. We have table employee we used in previous blog now we can create employee_history table from employee table with same structure

Below syntax you can use for creating employee_history table:

Create table employee_history as select * from employee.

--Creating Trigger on employee_history table as dotc_employee_history

Here we can create a trigger to update the employee_history  table when the salary column of the employee is updated in the employee table.

CREATE or REPLACE TRIGGER dotc_employee_history

BEFORE UPDATE OF salary

ON employee

FOR EACH ROW

BEGIN

INSERT INTO employee_history

VALUES

(:old.emp_id,

 :old.name,

 :old.address,

 :old.salary);

END;

/

Trigger is created now so when we issue update command in employee table for salary column data is updated in employee_history table automatically.

For example if we issue the below update statement

UPDATE EMPLOYEE SET SALARY = 50000 WHERE emp_id = 10001;

When above update query is executed, the trigger fires and updates the employee_history table automatically. We can use Rollback statement before committing update statement to the database, the data inserted in the table get also rolled back.

 

Let’s take example of Statement and Row Level Trigger by using BEFORE and AFTER statement:

Before Update in Statement Level Trigger:

Let's create a table 'employee_check' which we can use to store messages when triggers are fired.

 Let’s create the table by using below syntax:

CREATE TABLE employee_check

(msg varchar2(40),

 Curr_Date  Date

);

 

 

CREATE or REPLACE TRIGGER   DOTC2_Before_Updstate_employee

BEFORE

UPDATE ON employee

Begin

INSERT INTO employee_check

Values('Before update statement level',sysdate);

END;

/

 

Before Row Level Trigger Example using same table.

This trigger will insert a record into the table employee_check  before each row is updated.

 

 CREATE or REPLACE TRIGGER DOTC3_Before_Updrow_product

 BEFORE

 UPDATE ON product

 FOR EACH ROW

 BEGIN

 INSERT INTO product_check

 Values('Before update row level',sysdate);

 END;

 /

Once we update employee table above trigger will be fired

 

UPDATE employee SET salary = 55000

 WHERE emp_id in (10001,10002);

 

The record will be automatically inserted in employee_check table with message and date for both statement level trigger and row level Trigger.

To check the records you can use select from employee_check table to see the values.

 

Select * from employee_check;

 

So it’s for Audit also we can use statement level and row level trigger.

 

AFTER UPDATE in statement and row level trigger: This trigger will insert a record into the table employee_check after a sql update statement is executed, at the statement level or row level.

The syntax is almost same but we need to put After instead of Before. You can write Triggers also for after and it will also store the message and date information in employee_check table.

 

We have given example of Before/after Update only we can apply the same rules for INSERT and DELETE statements.

If you want to see the structure of Trigger you can use desc command.

Desc trigger DOTC3_Before_Updrow_product;

 

For Dropping Trigger we use Below command .

DROP TRIGGER DOTC3_Before_Updrow_product;

 

So Now we are very clear about Triggers and types of Trigger . Some Advantage of Triggers in PL/SQL is as below :

Generating some derived column values automatically.

Event logging and storing information on table access.

Auditing.

Synchronous replication of tables.

Imposing security authorizations.

 

Please go through this blog and try AFTER Update in both Row level and statement level trigger and you can also use insert and delete in Trigger. Try to write for both types of trigger for Audit and first trigger which I have given for inserting employee_history table you can try to write in with different clause .

 

Hope you are able to understand Triggers and it’s type . In interview also they will ask you questions from triggers so please read this blog carefully. For any doubt let me know.

Thanks.