Triggers are store procedure,
which are automatically executed or fired when some events occur like deletion, insertion of data.
Triggers are database object which is attached to a table and is executed
automatically. Triggers can’t be explicitly invoked.
Triggers could be defined on the schema, table, view or database with which an
event is associated.
Benefits of Triggers
can be benefited for the following purposes:
Enforcing referential integrity
Imposing security authorizations
Preventing invalid transactions
Synchronous replication of tables
Generating derived field values automatically
Event storing and logging information on table access
To add triggers in database schema CREATE TRIGGER statement is
used. Triggers are database operations that are automatically executed
when some events occur like deletion,
insertion of data.
A trigger is fired whenever a INSERT, UPDATE or DELETE, of
a particular database table occurs, or whenever an UPDATE occurs on
one or more specified field of a table. Following is
the syntax for creating a trigger:
[OR REPLACE ] TRIGGER name_of_trigger
| AFTER | INSTEAD OF }
[OR] | UPDATE [OR] | DELETE}
OLD AS old NEW AS new]
Declaration of a code block
In the above syntax,
[OR REPLACE ] TRIGGER name_of_trigger means : replaces or creates an existing trigger with the name_of_trigger.
| AFTER | INSTEAD OF } : This
specifies when the trigger is executed then INSTEAD OF is used for creating
[OR] | UPDATE [OR] | DELETE} : This
specifies the Data manipulation Language operation.
name_of_column] : This specifies the
name of column that would be updated.
name_of_table : T his specifies the
table name which is associated with the trigger.
OLD AS old NEW AS new] : This allows
you to refer old and new values for various Data Manipulation Language
statements, like UPDATE, INSERT and DELETE.
WHEN (condition1): This is a condition for rows
which the trigger would fire. This condition is true only for row level
[FOR EACH ROW]: This is a row trigger, i.e., the
trigger is executed when each row being affected.
Following is an example of trigger:
Select * from student
The below program creates a row level trigger for the student table that
would fire for DELETE or INSERT or UPDATE operations performed on the STUDENT
table. This trigger will display the marks difference between the new values
and old values:
CREATE OR REPLACE TRIGGER
BEFORE UPDATE OR DELETE OR INSERT ON
FOR EACH ROW
WHEN (NEW.ID > 0)
marks:= :NEW.marks -
dbms_output.put_line('Old marks: ' || :OLD. marks);
dbms_output.put_line('New marks: ' || :NEW. marks);
dbms_output.put_line(' marks difference: ' || marks);
As from the above example we can see when the
code is executed it produces the following result:
Now let’s perform some Data
Manipulation language (DML) operations on the STUDENT table. In the below
example one INSERT statement is given, which creates a new record in the table:
INTO STUDENT (ID, NAME, AGE, ADDRESS, marks)
(7, 'Ravi', 22, 'Chennai', 45 );
As from the above example, record has been
created in STUDENT table, now display_marks_changes will be executed and it will display
the following result: