Database Triggers in Oracle

                                          Triggers in Oracle
A Database trigger is:
•    A stored PL/SQL block associated with a table, a schema, or the database or
•    An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
Oracle Database automatically executes a trigger when specified conditions occur.
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement.
Prerequisites 
Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
•    To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
•    To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
•    In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
Insert Triggers:
BEFORE INSERT Trigger
AFTER INSERT Trigger

Update Triggers:
BEFORE UPDATE Trigger
AFTER UPDATE Trigger

Delete Triggers:
BEFORE DELETE Trigger
AFTER DELETE Trigger

Drop Triggers:
Drop a Trigger

Disable/Enable Triggers:
Disable a Trigger
Disable all Triggers on a table
Enable a Trigger
Enable all Triggers on a table

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax for an BEFORE INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2),
total_cost number(8,2), create_date date,created_by varchar2(10) );
We could then create a BEFORE INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
-- Update create_date field to current system date
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
END;

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.
The syntax for an AFTER INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2),
total_cost number(8,2));
We could then create an AFTER INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, username )
VALUES ( :new.order_id,:new.quantity,:new.cost_per_item,:new.total_cost, v_username );
END;

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.
The syntax for an BEFORE UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders 
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2),
updated_date    date,
updated_by    varchar2(10)
);

We could then create a BEFORE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE on the table
SELECT user INTO v_username
FROM dual;
-- Update updated_date field to current system date
:new.updated_date := sysdate;
-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;
END;

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);

We could then create an AFTER UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;     -- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity_before, quantity_after, username )
VALUES ( :new.order_id,:old.quantity, :new.quantity, v_username );
END;

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax for an BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);
We could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, delete_date, deleted_by )
VALUES( :old.order_id,:old.quantity,:old.cost_per_item,:old.total_cost,sysdate, v_username );
END;

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.
The syntax for an AFTER DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);
We could then create an DELETE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, delete_date, deleted_by)
VALUES( :old.order_id, :old.quantity, :old.cost_per_item, :old.total_cost, sysdate, v_username );
END;
Dropping a trigger:-
If you had a trigger called orders_before_insert, you could drop it with the following command:
DROP TRIGGER orders_before_insert;
Disabling and Enabling a trigger:-
If you had a trigger called orders_before_insert, you could disable it with the following command:
ALTER TRIGGER orders_before_insert DISABLE;
ALTER TABLE orders DISABLE ALL TRIGGERS;
ALTER TRIGGER orders_before_insert ENABLE;
ALTER TABLE orders ENABLE ALL TRIGGERS;
Firing Triggers One or Many Times (FOR EACH ROW Option):- 
The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.
For example, you define the following trigger:
CREATE TABLE Emp_log (Emp_id NUMBER, Log_date DATE, New_salary NUMBER, Action VARCHAR2(20));

CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Sal > 1000)
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');
END;

Then, you enter the following SQL statement:
UPDATE Emp_tab SET Sal = Sal + 1000.0 WHERE Deptno = 20;
If there are five employees in department 20, then the trigger fires five times when this statement is entered, because five rows are affected.
The following trigger fires only once for each UPDATE of the Emp_tab table:
CREATE OR REPLACE TRIGGER Log_emp_update
AFTER UPDATE ON Emp_tab
BEGIN
INSERT INTO Emp_log (Log_date, Action)
VALUES (SYSDATE, 'Emp_tab COMMISSIONS CHANGED');
END;

InsteadOf Triggers:-
Direct Triggers can’t be applied on views. For them InsteadOf triggers are applied. Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable.

Delete the Duplicate rows from a table:-

DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
Q. How many valid / invalid objects exist owned by this oracle user? 
Often we should know, how many valid and invalid objects an oracle user owes. Especially if we compare a schema on two different databases. 
SELECT DISTINCT (object_type) object, status, COUNT(*) FROM user_objects GROUP BY object_type, status;
This line implements sleep function in PL/SQL code. Put either in exception or inside <begin> <end> block.
DBMS_LOCK.SLEEP(10);

Get version of Oracle:-
select * from v$version;