Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Assignment No:6
Title: Triggers Implementation.
Problem Statement: Create a trigger that logs insert operations into a separate Logs table.
Course Objective: To acquire skills in SQL and PL/SQL for database operations.
Course Outcome: Implement database operations using SQL and PL/SQL constructs such as
procedures, functions, and triggers.
Tools Required: Oracle SQL Plus.
Theory:
1. Triggers
Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, 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).
1.1 Benefits of Triggers
Triggers can be written for the following purposes −
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
Syntax:
CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE} ON table_name
[FOR EACH ROW]
DECLARE
Declaration-statements
BEGIN
Executable-statements
END;
CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing
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.
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 1
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
[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 condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers.
1.2 BEFORE TRIGGER
Example
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.
The above 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.
CREATE TRIGGER SALTY BEFORE INSERT ON bank FOR EACH ROW
DECLARE
oper VARCHAR(10);
BEGIN
IF :NEW.ID<1 THEN
RAISE_APPLICATION_ERROR(-20001,'NUMBER MUST BE LARGE');
END IF;
END;
/
1.2 AFTER TRIGGER
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 2
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
CREATE TRIGGER TESTY AFTER UPDATE OR DELETE ON bank FOR EACH ROW
DECLARE
oper VARCHAR(10);
BEGIN
IF deleting THEN
oper:='deleting';
END IF;
INSERT INTO dummybank values(:old.id, :old.name,oper);
END;
/
Conclusion: Hence, we have successfully implemented Triggers.
Query and Output:
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 3
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 4
