PL/SQL Trigger
Triggers are
stored programs, which are automatically executed or fired when some events
occur.
Triggers are 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 can be
defined on the table, view, schema, or database with which the event is associated.
Benefits of Triggers
Triggers can be
written for the following 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 | 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;
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 will be executed. The INSTEAD OF a clause is used for creating triggers on a view.
· {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
· [OF col_name] − This specifies the column name that will 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 to new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
· [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will 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.
Example
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 |
+----+----------+-----+-----------+----------+
This trigger will display the salary difference between the old values and new values from the customer table−
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;
/
result −
Trigger created.
The following points need to be considered here −
· OLD and NEW references trigger to use for record-level not for table-level.
· This trigger will fire before any DELETE or INSERT or UPDATE operation on the table.
Triggering a Trigger
When we perform some DML operations on the CUSTOMERS table then the trigger is triggering.
Ex.
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
display_salary_changes will be fired and it will display the following result −
Old salary:
New salary: 7500
Salary difference:
We insert a new record, then the value of old salary and Salary difference are not displayed.
When we use the UPDATE statement on the customer table.
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
display_salary_changes will be fired and it will display the following result −
Old salary: 1500
New salary: 2000
Salary difference: 500
Parts of a Trigger
Three sequential parts:
Triggering Event or Statement:
The statement which reason creates a trigger is called a triggering event or statement.
This can be DDL statements, DML statements, or any database operation, for fire a trigger.
Trigger Restriction:
The condition or any limitation applied to the trigger is called trigger restriction.
If such a condition is TRUE then the trigger occurs otherwise it does not occur.
Trigger Action:
The body containing the executable statements that are to be executed when a trigger occurs till the Trigger restriction condition is True.
Types of Triggers
Triggers can be classified into three categories:
Level Triggers
Event Triggers
Timing Triggers
Level Triggers
There are 2 different types of level triggers, they are:
ROW LEVEL TRIGGERS
It fires for every record that got affected with the execution of DML statements like INSERT, UPDATE, DELETE etc.
It always uses a FOR EACH ROW clause in a triggering statement.
STATEMENT LEVEL TRIGGERS
It fires once for each statement that is executed.
Event Triggers
There are 3 different types of event triggers, they are:
DDL EVENT TRIGGER
It fires with the execution of every DDL statement(CREATE, ALTER, DROP, TRUNCATE).
DML EVENT TRIGGER
It fires with the execution of every DML statement(INSERT, UPDATE, DELETE).
DATABASE EVENT TRIGGER
It fires with the execution of every database operation which can be LOGON, LOGOFF, SHUTDOWN, SERVER ERROR, etc.
Timing Triggers
There are 2 different types of timing triggers, they are:
BEFORE TRIGGER
It fires before executing the DML statement.
The triggering statement may or may not executed depending upon the before condition block.
AFTER TRIGGER
It fires after executing the DML statement.
0 Comments