PL/SQL Trigger

Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger is stored into database and invoked repeatedly, when specific condition match.

Triggers are stored programs, which are automatically executed or fired when some event occurs.

Triggers are written to be executed in response to any of the following events.

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Advantages of Triggers

These are the following advantages of Triggers:

Trigger generates some derived column values automatically
Enforces referential integrity
Event logging and storing information on table access
Auditing
Synchronous replication of tables
Imposing security authorizations
Preventing invalid transactions

Creating a trigger:

Syntax for creating trigger:

  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)   DECLARE    Declaration-statements BEGIN     Executable-statements EXCEPTION    Exception-handling-statements END;  

Here,

CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces an existing trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
[OF col_name]: This specifies the column name that would be updated.
[ON table_name]: This specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
[FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

PL/SQL Trigger Example

Let’s take a simple example to demonstrate the trigger. In this example, we are using the following CUSTOMERS table:

Create table and have records:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

Create trigger:

Let’s take a program to create a row level trigger for the CUSTOMERS table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

  CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE    sal_diff number; BEGIN    sal_diff := :NEW.salary  – :OLD.salary;    dbms_output.put_line(‘Old salary: ‘ || :OLD.salary);    dbms_output.put_line(‘New salary: ‘ || :NEW.salary);    dbms_output.put_line(‘Salary difference: ‘ || sal_diff); END; / 

After the execution of the above code at SQL Prompt, it produces the following result.

Trigger created.

Check the salary difference by procedure:

Use the following code to get the old salary, new salary and salary difference after the trigger created.

 DECLARE     total_rows number(2); BEGIN    UPDATE  customers    SET salary = salary + 5000;    IF sql%notfound THEN       dbms_output.put_line(‘no customers updated’);    ELSIF sql%found THEN       total_rows := sql%rowcount;       dbms_output.put_line( total_rows || ‘ customers updated ‘);    END IF;  END; / 

Output:

Old salary: 20000 New salary: 25000 Salary difference: 5000 Old salary: 22000 New salary: 27000 Salary difference: 5000 Old salary: 24000 New salary: 29000 Salary difference: 5000 Old salary: 26000 New salary: 31000 Salary difference: 5000 Old salary: 28000 New salary: 33000 Salary difference: 5000 Old salary: 30000 New salary: 35000 Salary difference: 5000 6 customers updated

Note: As many times you executed this code, the old and new both salary is incremented by 5000 and hence the salary difference is always 5000.

After the execution of above code again, you will get the following result.

Old salary: 25000 New salary: 30000 Salary difference: 5000 Old salary: 27000 New salary: 32000 Salary difference: 5000 Old salary: 29000 New salary: 34000 Salary difference: 5000 Old salary: 31000 New salary: 36000 Salary difference: 5000 Old salary: 33000 New salary: 38000 Salary difference: 5000 Old salary: 35000 New salary: 40000 Salary difference: 5000 6 customers updated

Important Points

Following are the two very important point and should be noted carefully.

OLD and NEW references are used for record level triggers these are not avialable for table level triggers.
If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Next TopicPL/SQL Interview Questions

PL/SQL Trigger

PL/SQL Trigger

Advantages of Triggers

Creating a trigger:

PL/SQL Trigger Example

Important Points

Polymorphism in PHP

What is Polymer.js

You may also like