Triggers:
In this post we will learn about Triggers in Oracle PLSQL,
A Trigger is a procedure that will fire automatically by the database. When a
specified
DML statement is run against the specified table. Triggers are useful for doing
things
like advanced auditing of changes made to a column values in a table. The
syntax
for
creating the trigger is given below.
 |
| Triggers in PL/SQL |
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE
| AFTER | INSTEAD OF} trigger_event
ON
table_name
[FOR
EACH ROW [WHEN trigger_condition]]
BEGIN
Trigger_body;
END;
Ex:
This example is when we insert into a table t1 then the same data has to be
inserted
into
the table t2. For that we require two tables of one same column.
SQL>
CREATE TABLE t1(
Eno
number);
SQL>
CREATE TABLE t2(
Eno
number,
Ins_date
date);
SQL>
CREATE OR REPLACE TRIGGER razia_trigger
BEFORE
INSERT ON TABLE t1
FOR
EACH ROW
BEGIN
INSERT
INTO TABLE t2
VALUES(:new.eno,sysdate);
END;
Note:
For every insert operation on t1 it will insert the eno col values to t2 along
with the current
sysdate
in the other column.
Disabling
and Enabling Trigger:
We can stop the trigger from firing by disabling the
trigger
by using the ALTER TRIGGER statement. The syntax for enabling the trigger and
disabling
the trigger is given below.
Syntax:
ALTER TRIGGER trigger_name DISABLE;
ALTER
TRIGGER trigger_name ENABLE;
Dropping
a Trigger:
If we want to drop the trigger from the database then we have to
use
DROP TRIGGER statement. The syntax for dropping the trigger is given below.
Syntax:
DROP TRIGGER trigger_name;
