Triggers are known to be the stored programs, that are executed automatically, or even fired when some events occur. In response to any of the following events, triggers are said to be executed. Triggers are said to be defined on the table, view, schema or database where the events are associated.
- 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)
Creating triggers:
Syntax:
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 – this creates or replaces an existing trigger with the given trigger_name.
- {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger is executed. The INSTEAD OF clause can be used for creating trigger on a view.
- {INSERT [OR] | UPDATE [OR] | DELETE} – it specifies the DML operation.
- [OF col_name] – it specifies the column name that should be updated.
- [ON table_name] − it specifies the name of the table associated with that of the trigger.
- [REFERENCING OLD AS o NEW AS n] − This allows us for referring new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
- [FOR EACH ROW] – It specifies a row-level trigger.
Example:
Select * from customers;
Refer to the table we created in the functions section:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON teachers
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;
/
Output:
Trigger created.
Triggering the Trigger:
INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Ranjitha', 34, 'bangalore', 3500.00);
display salary_changes;
Old salary:
New salary: 3500
Salary difference: