(12) PL/SQL triggers

A trigger is a stored program that automatically executes or emits when some event occurs. Triggers, in fact, write responses to any of the following events will be executed:
Database Operations (DML) statements (delete,insert,update or)
Database definition (DDL) statements (create,alter or drop)
Database Operations (Servererror, logon, logoff, startup or shutdown)
A trigger can be defined on a table, view, schema, or database associated with the event.

Advantages OF Triggers
automatically generate some derived column values
Enforcing referential Integrity
event logs and access to tables store information
Audits
synchronous replication of tables
Implement a security license
Preventing illegal transactions
  
  
first, create a trigger
syntax for creating triggers:
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;
So,
create [or replace] TRIGGER trigger_name: Creates or replaces an existing trigger: Trigger_name
{before | After | INSTEAD of}: Specifies when the trigger is to be executed. The instead of clause is used to create a trigger in a view
{INSERT [OR] | UPDATE [OR] | DELETE}: Specify DML operation
[of Col_name]: Specifies the name of the column to be updated
[on table_name]: Specifies the name of the table associated with the trigger
[referencing old as O new as n]: various DML statements, such as insert,update and delete, that can refer to the new and previous values
[for every row]: Specifies a row-level trigger that triggers each row to be affected. Otherwise, when executing the SQL statement, this is called a table-level trigger trigger that will be executed once
When (condition): The condition that the trigger will trigger. This clause applies only to row-level triggers that are valid

Example:
first Use the Customers table:
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 in the Customers table that will trigger an INSERT or UPDATE or delete operation on the Customers table. The trigger displays the difference between the old and new values of the payroll:

CREATE or REPLACE TRIGGER display_salary_changesbefore DELETE or INSERT or UPDATE on Customersfor each rowwhen (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 following result: Trigger created.

here the following two points are important and should be carefully noted:
Old and new references are not available for table-level triggers, and they cannot be used for record-level triggers.
if you want to query the same trigger in the table, then the app should use the After keyword, because the trigger can query the table, or change it again the original changes only apply after the table is back to the consistent state.
The above trigger has been written in such a way that it executes before any DELETE, insert, or update operation on the table, but can take one or more actions on the trigger, such as before DELETE, This will be triggered whenever a record will be deleted using the delete operation on the table.
 
 
 
second, trigger a trigger
Let's perform some DML operations on the Customers table. Here is an INSERT statement, which creates a new record in the table:
INSERT into CUSTOMERS (id,name,age,address,salary)VALUES (7, ' Kriti ', +, ' HP ', 7500.00);
when the record is created in the Customers table, the above trigger display_salary_changes will be triggered and it will show the following results:
Old Salary:
New salary:7500
Salary Difference:
because this is a new record, the base pay is not available and the result above is empty (null). Now, let's do one more DML operation on the Customers table. Here is an UPDATE statement that will update the existing records in the table:
UPDATE Customers
SET Salary = salary +
WHERE id = 2;
when the record is updated in the Customers table, the above-created trigger display_salary_changes will be triggered, and it will show the following results:
Old salary:1500
New salary:2000
Salary difference:500

(12) PL/SQL triggers