Triggers in Database Management System(DBMS).

Triggers:

Triggers are defines as stored procedure which acts as object ,it executes automatically based on event taking place specified in trigger creation.

It is used for maintain constraints.

Benefits: 

1. Trigger are used to enforce integrity constraints in the database.

2. Since trigger model is based on ECA model (Event Condition Action) and hence it execute on based on the event taking placed and it does not required a human intervention.

3. Since it does not required any human intervention , it will maintain the accuracy of value in the table, as every time event places , trigger is fired on its own.

4. As triggers are always present in main memory of database , if it is run time (dynamic) features of database if it is implement by taking needed numbers of trigger only, will optimize performance of database .

Drawbacks:     

Extensive use of trigger may slow down the database performance as trigger when created , remains in primary memory.

    

Syntax:

    CREATE TRIGGER trigger_name

    AFTER|BEFORE|INSTEAD OF

    DELETE|UPDATE|INSERT

    [of column_name] 

    ON table_name

    [REFERENCING OLD  variable_name1 AS NEW AS variable_name2]

    [FOR EACH ROW  O\N]

    [WHEN condition ]

    DECLARE 

        ....................BLOCK.........

    BEGIN

        ....................BLOCK.........

    EXCEPTION

        ....................BLOCK.........

    END;

'CREATE TRIGGER' is keyword which is followed by trigger_name.

'AFTER | BEFORE | INSTEAD OF' is used to specify whether the trigger will be fired before or after event.

'INSTEAD OF' is  used for specify triggers on view.

'DELETE | UPDATE | INSERT' is a DML statement which specifies the event an column name into a particular table.

'OF COLUMN' is optional i.e. a trigger may be specify directly for a table. When ever a DML statement is executed database creates two copies of the record updated where old specifies the original records and new specifies the updated records. Old and new can be references as smaller  names such as 'O' and 'N'.

'FOR EACH ROW' specifies that trigger t be executed on every row is updated , If each row  is not specified then trigger will be executed after completion of records.

We can specify the condition also such that trigger executes only if condition is satisfied . After when trigger consists of the structure of the block.   

Example:

Create a trigger to update the products table after orders is processed.

Syntax:

CREATE TRIGGER trigger_name 

AFTER

INSERT 

ON products

REFERENCING OLD AS O NEW AS n 

................................................................

................................................................