The trigger is a special type of stored procedure. It gets invoked automatically when an event like INSERT, UPDATE, DELETE, etc. happens on the database server. You can use it, for example, to enforce referential integrity in the database. Suppose you want to delete an author from the ‘authors’ table. You can use triggers to delete all rows in the ‘books’ table which has ‘author_id’ as of the deleted author.
Types of triggers :
DML trigger : DML trigger gets fired whenever a user tries to manipulate data using DML(Data Manipulation Language) event on the database server. DML events are INSERT, DELETE, or UPDATE.
DDL trigger : DDL trigger gets fired whenever a user tries to manipulate data using DDL(Data Definition Language) event on the database server. DDL events are CREATE, ALTER, DROP, etc.
Logon trigger : Logon trigger gets fired when a LOGON event is raised whenever a user’s session is created.
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT | UPDATE | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
