In this section, we will examine Triggers in PL/SQL. Triggers are put away projects, which are naturally executed or terminated when a few occasions happen. Triggers are, indeed, composed to be executed in light of any of the accompanying occasions −
- An information base control (DML) explanation (DELETE, INSERT, or UPDATE)
- An information base definition (DDL) explanation (CREATE, ALTER, or DROP).
- An information base activity (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be characterized on the table, view, diagram, or information base with which the occasion is related.
Benefits of Triggers
Triggers can be composed for the accompanying purposes −
- Producing some determined segment esteems consequently
- Upholding referential uprightness
- Occasion logging and putting away data on table access
- Evaluating
- Simultaneous replication of tables
- Forcing security approvals
- Forestalling invalid exchanges
Creating Triggers
The linguistic structure for making 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,
- Make [OR REPLACE] TRIGGER trigger_name − Creates or replaces a current trigger with the trigger_name.
- {BEFORE | AFTER | INSTEAD OF} − This determines when the trigger will be executed. The INSTEAD OF provision is utilized for making trigger on a view.
- {INSERT [OR] | UPDATE [OR] | DELETE} − This determines the DML activity.
- [OF col_name] − This determines the segment name that will be refreshed.
- [ON table_name] − This determines the name of the table related with the trigger.
- [REFERENCING OLD AS o NEW AS n] − This permits you to allude new and old qualities for different DML proclamations, for example, INSERT, UPDATE, and DELETE.
- [FOR EACH ROW] − This indicates a line level trigger, i.e., the trigger will be executed for each column being influenced. In any case the trigger will execute just once when the SQL explanation is executed, which is known as a table level trigger.
- At the point when (condition) − This gives a condition to lines for which the trigger would fire. This provision is legitimate just for column level triggers.
Model
Most importantly, we will utilize the CUSTOMERS table we had made and utilized in the past sections −
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 accompanying project makes a column level trigger for the clients table that would fire for INSERT or UPDATE or DELETE activities performed on the CUSTOMERS table. This trigger will show the compensation contrast between the old qualities and new qualities −
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;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Trigger created.
The accompanying focuses should be considered here −
- OLD and NEW references are not accessible for table-level triggers, rather you can utilize them for record-level triggers.
- On the off chance that you need to question the table in a similar trigger, at that point you should utilize the AFTER catchphrase, since triggers can inquiry the table or change it again exclusively after the underlying changes are applied and the table is back in a predictable state.
- The above trigger has been written so that it will fire before any DELETE or INSERT or UPDATE procedure on the table, however you can compose your trigger on a solitary or numerous tasks, for instance BEFORE DELETE, which will fire at whatever point a record will be erased utilizing the DELETE procedure on the table.
Triggering a Trigger
Allow us to play out some DML procedure on the CUSTOMERS table. Here is one INSERT explanation, which will make another record in the table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
At the point when a record is made in the CUSTOMERS table, the above make trigger, display_salary_changes will be terminated and it will show the accompanying outcome −
Old salary:
New salary: 7500
Salary difference:
Since this is another record, old compensation isn't accessible and the above outcome comes as invalid. Allow us presently to perform one more DML procedure on the CUSTOMERS table. The UPDATE explanation will refresh a current record in the table −
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
At the point when a record is refreshed in the CUSTOMERS table, the above make trigger, display_salary_changes will be terminated and it will show the accompanying outcome −
Old salary: 1500
New salary: 2000
Salary difference: 500