Trigger is stored pl/sql
unit associated with a database table or view. Also it defines some action in
the database whenever some database manipulation take place like INSERT, UPDATE
and DELETE.
Unlike stored procedure and functions (which
will be called explicitly), triggers are fires (executed) or called
implicitly while running the DML/DDL statements in the DB.
Syntax:
CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING
[NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW
[WHEN (<trigger_condition>)]]
<trigger_body>
This has 3 parts
1.
A triggering event (insert,
update, delete or instance shutdown or start-up etc...)
2.
A trigger constraint (optional)
(this specifies the Boolean expression that must be true for the trigger to
fire. This we can have in the WHEN clause.)
3.
Trigger action, it’s a
procedure that contains the code to be executed when the trigger fires.
Types
1.
Row triggers and statement
triggers
A row trigger
fires once for each row effected. It uses FOR EACH clause.
2.
Before and after triggers
1.
BEFORE triggers
2.
AFTER triggers
3.
INSTEAD of triggers
4.
LOGON triggers
Note:
1.
Cannot include COMMIT,
SAVEPOINT and ROLLBACK
2.
We can use only one trigger of
a particular type
3.
A table can have any number of
triggers
4.
We use correlation names :new and :old can be used to refer to data in
command line and data in table respectively.
DDL Triggers
1.
BEFORE CREATE OR AFTER CREATE
is fired when a schema object is created
2.
BEFORE OR AFTER ALTER trigger
is fired when a schema object is altered
3.
BEFORE OR AFTER DROP trigger is
fired when a schema object is dropped.
Enabling and disabling triggers
A trigger is
automatically enabled when it is created.
SQL
Commands work with TRIGGERS
Enable/Disable specific
trigger:
ALTER
TRIGGER trigger_name DISABLE;
ALTER
TRIGGER trigger_name ENABLE;
Enable/Disable
all triggers
ALTER
TRIGGER table_name DISABLE ALL TRIGGERS;
ALTER
TRIGGER table_name ENABLE ALL TRIGGERS;
Drop
a trigger
DROP
TRIGGER trigger_name