Triggers in Oracle

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or
view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example,
when a new record (representing a new Employee) is added to the Employee_Info table, new records should be created also in the
tables of the taxes, vacations, and salaries.

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.
1.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.
2.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.
3.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.
Examaple :
Create A Table On books where bookid,authorname,price,quantity,edition etc are stored .There are also another table where
sale information are stored.

CREATE TABLE books(
bookname varchar2(20),
authorname varchar2(30),
quantity integer,
price number(10,2),
edition varchar2()
);

CREATE TABLE sales(
saleid integer,
bookname varchar2(20) FOREIGN KEY,
price number(10,2),
quantity integer
);

when a record insert into sales table then quantity of corresponding book in books table must be updated. we will do it
using trigger . This trigger run when a record insert into sales table.

CREATE OR REPLACE TRIGGER Book_TRIGGER
AFTER INSERT ON sales
FOR EACH ROW
DECLARE

BEGIN
UPDATE books SET quantity=quantity-:new.quantity where bookname=:new.bookname;
END;
/

if books table have a book named teach yourself C++ which quantity is 20. IF A teach yourself C++ book sells then teach yourself C++ book will
book's quantity will be 19 .we will do it using above Book_TRIGGER trigger.