- A trigger is a PL/SQL block structure which is triggered (executed) automatically when DML statements like Insert, Delete, and Update is executed on a table.
- There are two types of triggers based on the level it is triggered.
- Row level trigger - An event is triggered when any row of the table is changed irrespective of insert, update or delete statement.
- Statement level trigger - An event is triggered when particular SQL statement is executed, e.g. trigger on insert command.
- We cannot pass parameters into triggers like stored procedure.
- We cannot do transaction control (commit … rollback) in trigger.
- Triggers are normally slow.
- When triggers can be used,
- Based on change in one table, we want to update other table.
- Automatically update derived columns whose values change based on other columns.
- Logging.
- Enforce business rules.
Syntax of Trigger
CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE / AFTER]
[INSERT / UPDATE / DELETE [of columnname]]
ON table_name
[REFERENCING [OLD AS old, NEW AS new]]
[FOR EACH ROW [WHEN condition]]
DECLARE
Declaration section
BEGIN
Executable statements
END;
- CREATE [OR REPLACE ] TRIGGER trigger_name:- This clause creates a trigger with the given name or overwrites an existing trigger.
- [BEFORE | AFTER]:- This clause indicates at what time the trigger should be fired. Before updating the table or after updating a table.
- [INSERT / UPDATE / DELETE]:- This clause determines on which kind of statement the trigger should be fired. Either on insert or update or delete or combination of any or all. More than one statement can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
- [OF columnname]:- This clause is used when you want to trigger an event only when a specific column is updated. This clause is mostly used with update triggers.
- [ON table_name]:- This clause identifies the name of the table or view to which the trigger is related.
- [REFERENCING OLD AS old NEW AS new]:- This clause is used to reference the old and new values of the data being changed. By default, you reference the values as old. column_name or new.column_name. The reference names can also be changed from old or new to any other user-defined name. You cannot reference old values while inserting a record, or new values while deleting a record, because they do not exist.
- [FOR EACH ROW]:- This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire SQL statement is executed(i.e.statement level Trigger).
- WHEN (condition):- The trigger is fired only for rows that satisfy the condition specified. This clause is valid only for row level triggers.
Example:
We are creating trigger that display message if we insert negative value or update value to negative value in bal column of account table.
CREATE OR REPLACE TRIGGER balnegative
BEFORE insert OR update
On account
FOR EACH ROW
BEGIN
IF :NEW.bal< 0 THEN
dbms.output.put.line (‘balance is negative’)
END IF;
END;
OUTPUT:-
Trigger is created.
- Now when you perform insert operation on account table.
- SQL:> Insert into account (bal) values (-2000); OR
- SQL:> Update account set bal=-5000 where bal=1000;
- It displays following message before executing insert or update statement.
Output:- Balance is negative.
We get message that balance is negative it indicates that trigger has executed before the insertion or update operation.
