In this blog, we’ll discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the subsequent 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 are often defined on the table, view, schema, or database with which the event is associated.
Benefits of Triggers
Triggers are often written for the subsequent purposes −
• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions
Creating Triggers
The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | rather than } 
{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;

Where,
• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger are going to be executed. The rather than clause is employed for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
• [OF col_name] − This specifies the column name which will be updated.
• [ON table_name] − This specifies the name of the table related to the trigger.
• [REFERENCING OLD AS o NEW AS n] − this enables 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 are going to be executed for every row being affected. Otherwise the trigger will execute just one occasion when the SQL statement is executed, which is named a table level trigger.
• WHEN (condition) − This provides a condition for rows that the trigger would fire. This clause is valid just for row-level triggers.
Example
To start with, we’ll be using the purchasers table we had created and utilized in the previous chapters −
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+—-+———-+—–+———–+———-+
The following program creates a row-level trigger for the purchasers table that might fire for INSERT or UPDATE or DELETE operations performed on the purchasers 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; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Trigger created.
The following points got to be considered here −
• OLD and NEW references aren’t available for table-level triggers, rather you’ll use them for record-level triggers.
• If you would like to question the table within the same trigger, then you ought to use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and therefore the table is back during a consistent state.
• The above trigger has been written in such how that it’ll fire before any DELETE or INSERT or UPDATE operation on the table, but you’ll write your trigger on one or multiple operations, for instance BEFORE DELETE, which can fire whenever a record are going to be deleted using the DELETE operation on the table.
Triggering a Trigger
Let us perform some DML operations on the purchasers table. Here is one INSERT statement, which can create a replacement record within the table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, ‘Kriti’, 22, ‘HP’, 7500.00 );
When a record is made within the CUSTOMERS table, the above create trigger, display_salary_changes are going to be fired and it’ll display the subsequent result −
Old salary:
New salary: 7500
Salary difference:
Because this is often a replacement record, old salary isn’t available and therefore the above result comes as null. allow us to now perform another DML operation on the purchasers table. The UPDATE statement will update an existing record within the table −
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
When a record is updated within the CUSTOMERS table, the above create trigger, display_salary_changes are going to be fired and it’ll display the subsequent result −
Old salary: 1500
New salary: 2000
Salary difference: 500
So, this brings us to the end of blog. This Tecklearn ‘Triggers in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

454