Active data based and triggers

 Active data based and triggers

Triggers are procedures that are automatically called by the DBMS in response to changes to the database and are specified by the database administrator (DBA). A database that has a set of triggers associated with it is usually called an active database.

parts of the trigger

A trigger description consists of the following three parts −

· Event - An event is a change to the database that activates a trigger.

· Conditional - The query is invoked as a condition and is executed when the trigger is activated.

· Action - the procedure that is executed when the trigger is activated and its condition is true. Using triggers

Triggers can be used for any of the following reasons −

· Implement complex business rules that cannot be implemented due to constraints.

· Triggers are used to test processes. For example, track changes to a spreadsheet.

· Triggers are used to execute automatic actions when another affected action occurs.

Trigger type

Different types of triggers are described below −

· Statement-level triggers - fire only once for a DML statement, regardless of how many rows the statement affects. A statement-level trigger is a standard type of trigger.

· Before-Triggers - When defining a trigger, you can specify whether the trigger fires before a command such as INSERT, DELETE, or UPDATE is executed, or after the command is executed. A Before trigger is automatically used to check the validity of the data before executing the action. For example, you can use a before trigger to prevent row deletion if you don't allow row deletion in certain cases.

· After-Triggers - Used after the trigger action has completed. For example, if a trigger is associated with an INSERT command, the trigger fires after a row is inserted into the table.

· Row-level triggers - fire for each row affected by a DML command. For example, if an UPDATE command updates 150 rows, row-level triggers fire 150 times, but statement-level triggers fire only once.

Create a database trigger

Use the CREATE TRIGGER command to create database triggers. The details to be specified while creating the trigger are as follows:-

•        The trigger's given name.
•        Table it should be connected to.
•        Whether the trigger should be triggered before or after. 
•        A command such as UPDATE, DELETE, or INSERT that activates the trigger.
•        Whether row-level triggers are used or not.
•        Filter rows with a condition.

 

The syntax for creating a database trigger is −

CREATE [OR REPLACE] TRIGGER trigger-name

{before | after}

{DELETE|INSERT|UPDATE[OF COLUMNS]} on the table

[FOR EACH ROW {IF condition]]

[reference [old] [new]]

start

PL/SQL block

end.

 

References:

Research team

1.     Rohan Mahjan

2.     Tejas Mahajan

3.     Suraj Chaudhari 

4.     Khushi Junnare