- Triggers are Stored in database and executed by Oracle engine whenever some event occurs.
- When a trigger is fired, SQL statement inside the trigger's PL/SQL code block can also fire the same or some other trigger. This is called cascading triggers.
- Triggers are written to execute in response events like DML Statements (DELETE, INSERT, or UPDATE), DDL statements (CREATE, ALTER, DELETE), and database operation (SERVERERROR, LOGON, LOGOFF, STARTUP or SHUTDOWN)
Difference between Trigger and Stored Procedure
| Trigger | Stored Procedure |
|---|---|
| Trigger is an act which is performed automatically before or after an event has occurred. | Stored procedure is a set of functionality which is executed when it is explicitly invoked. |
| It cannot accept parameters. | It can accept parameters. |
| A trigger cannot return any value. | A stored procedure can return a value. |
| It is executed automatically on some event. | It needs to be explicitly called. |
| Triggers are used for insertion, update and deletion. | Stored procedures are often used independently in the database. |
Uses of Triggers
Triggers are used to:1. Generate virtual column values automatically.
2. Log events.
3. Prevent invalid transactions.
4. Enforce complex business or referential integrity rules that user cannot define with constraints.
DML Triggers
DML triggers are created on table or view, and their triggering event that is composed of the DML statements DELETE, INSERT, UPDATE.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;
Example: Illustration of creating trigger in PL/SQL.
Consider the following table titled 'Employee'.
| Id | Name | Designation | Salary |
|---|---|---|---|
| 1 | Albert | Programmer | 50000 |
| 2 | Anna | HR | 25000 |
| 3 | Mark | Analyst | 55000 |
| 4 | Jason | Content writer | 20000 |
| 5 | Andrew | Programmer | 90000 |
Lets take a program to create a row level trigger for the table 'Employee' that will fire for INSERT, or UPDATE or DELETE operations performed on the table 'Employee'. This trigger will display the difference between the old salary and new salary.
CREATE OR REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.Id>0)
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/
The trigger will be created after executing the above code.
The following code is used to check the salary difference.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 1000;
IF sql%notfound THEN
dbms_output.put_line('no employee updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' employee updated ');
END IF;
END;
/
Output:
New salary:51000
Old salary: 50000
Salary difference:1000
New salary:26000
Old salary: 25000
Salary difference:1000
New salary:56000
Old salary: 55000
Salary difference:1000
New salary:21000
Old salary: 20000
Salary difference: 1000
New salary:91000
Old salary: 90000
Salary difference:1000
