Triggers IN SQL
Triggers
Triggers are stored programs, which are
automatically executed or fired when some
events occur.
Triggers are, written to be executed in
response to events such as:
A Database Manipulation (DML) statement
(DELETE, INSERT, or UPDATE)
Triggers
A Database Definition (DDL) Statement
(CREATE, ALTER, or DROP).
A Database Operation (SERVERERROR,
LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers
Triggers can be defined on the table, view,
schema, or database with which the event is
associated.
Benefits of Triggers
Generating some derived column values
automatically
Enforcing referential integrity
Event logging and storing information on table
access
Auditing
Synchronous replication of tables
Imposing security authorizations
Preventing invalid transactions
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;
CREATE [OR REPLACE] TRIGGER trigger_name
− Creates or replaces an exis ng trigger with
the trigger_name.
{BEFORE | AFTER | INSTEAD OF} − This
specifies when the trigger will 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 will 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, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] − This specifies a row-level
trigger, i.e., the trigger will 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 condi on
for rows for which the trigger would fire. This
clause is valid only for row-level triggers.
Select * from customers;
+----+----------+---------+--------------------+----------------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+------------+---------+--------------------+---------------+
| 1 | Pranav | 32 | Mumbai | 90000.00 |
| 2 | Yogesh | 25 | Navi Mumbai | 65000.00 |
| 3 | Pritesh | 23 | Karnataka | 80000.00 |
| 4 | Sheetal | 25 | Mumbai | 65000.00 |
| 5 | Arpita | 27 | Bhopal | 85000.00 |
| 6 | Bhumi | 22 | Delhi | 45000.00 |
+----+------------+----------+----------------+--------------+
Write a program creates 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;
OLD and NEW references are not available for
table-level triggers, rather you can use them
for record-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.
Here trigger has been written in such a way
that it will fire before any DELETE or INSERT or
UPDATE operation on the table, but you can
write your trigger on a single or multiple
operations.
For example BEFORE DELETE, which will fire
whenever a record will be deleted using the
DELETE operation on the table.
Let us perform some DML operations on the
CUSTOMERS table.
Here is one INSERT statement, which will
create a new record in the table
INSERT INTO CUSTOMERS
(ID,NAME,AGE,ADDRESS,SALARY) VALUES (7,
'Kriti', 22, 'HP', 75000.00 );
Let us perform some DML operations on the
CUSTOMERS table.
Here is one INSERT statement, which will
create a new record in the table
INSERT INTO CUSTOMERS
(ID,NAME,AGE,ADDRESS,SALARY) VALUES (7,
'Kriti', 22, 'HP', 75000.00 );
When a record is created in the CUSTOMERS
table, the trigger, display_salary_changes will
be fired and it will display the following result
Old salary: New salary: 75000
Salary difference:
Because this is a new record, old salary is not
available and the above result comes as null.
The UPDATE statement will update an existing
record in the table −
UPDATE customers SET salary = salary + 5000
WHERE id = 2;
When a record is updated in the CUSTOMERS
table, the trigger, display_salary_changes will be
fired and it will display the following result −
Old salary: 65000 New salary: 20000 Salary
difference: 85000
