PL/SQL Trigger
Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger is stored into database and invoked repeatedly, when specific condition match.
Triggers are stored programs, which are automatically executed or fired when some event occurs.
Triggers are written to be 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 could be defined on the table, view, schema, or database with which the event is associated.
Advantages of Triggers
These are the following advantages of Triggers:
- Trigger generates some derived column values automatically
- Enforces referential integrity
- Event logging and storing information on table access
- Auditing
- Synchronous replication of tables
- Imposing security authorizations
- Preventing invalid transactions
Creating a trigger:
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;
Here,
- CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces an existing trigger with the trigger_name.
- {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
- {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
- [OF col_name]: This specifies the column name that would 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 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 would 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.
PL/SQL Trigger Example
Let's take a simple example to demonstrate the trigger. In this example, we are using the following CUSTOMERS table:
Create table and have records:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 23 | Allahabad | 20000 |
| 2 | Suresh | 22 | Kanpur | 22000 |
| 3 | Mahesh | 24 | Ghaziabad | 24000 |
| 4 | Chandan | 25 | Noida | 26000 |
| 5 | Alex | 21 | Paris | 28000 |
| 6 | Sunita | 20 | Delhi | 30000 |
Create trigger:
Let's take a program to create a row level trigger for the CUSTOMERS table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS 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;
/
After the execution of the above code at SQL Prompt, it produces the following result.
Trigger created.
Check the salary difference by procedure:
Use the following code to get the old salary, new salary and salary difference after the trigger created.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/
Output:
Old salary: 20000 New salary: 25000 Salary difference: 5000 Old salary: 22000 New salary: 27000 Salary difference: 5000 Old salary: 24000 New salary: 29000 Salary difference: 5000 Old salary: 26000 New salary: 31000 Salary difference: 5000 Old salary: 28000 New salary: 33000 Salary difference: 5000 Old salary: 30000 New salary: 35000 Salary difference: 5000 6 customers updated
Note: As many times you executed this code, the old and new both salary is incremented by 5000 and hence the salary difference is always 5000.
After the execution of above code again, you will get the following result.
Old salary: 25000 New salary: 30000 Salary difference: 5000 Old salary: 27000 New salary: 32000 Salary difference: 5000 Old salary: 29000 New salary: 34000 Salary difference: 5000 Old salary: 31000 New salary: 36000 Salary difference: 5000 Old salary: 33000 New salary: 38000 Salary difference: 5000 Old salary: 35000 New salary: 40000 Salary difference: 5000 6 customers updated
Important Points
Following are the two very important point and should be noted carefully.
- OLD and NEW references are used for record level triggers these are not avialable for table level triggers.
- If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
0 Comments