DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database

Trigger in DBMS

Trigger in DBMS: A trigger is a procedure of SQL statements, which is automatically fired when the DML statements are executed on the table of the database. Triggers are the event-driven procedures, which are managed and stored by the database management system.

Trigger helps in maintaining data integrity by changing the database data in a systematic way. Triggers are always associated with the insert or update or delete command of the database table.

Syntax of Trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [ OR ] | UPDATE [ OR ] | DELETE}
[OF name_of_column]
ON name_of_the_table 
REFERENCING [ OLD AS old | NEW AS new ] 
[ FOR EACH ROW | FOR EACH STATEMENT ]  
WHEN (condition)
DECLARE
[ declaration_section ]  
 BEGIN   
  --- sql statements 
  END;

Explanation of each statement is described below:

CREATE [OR REPLACE ] TRIGGER trigger_name:

This statement in the syntax creates a trigger in the database with the specified name. It also overwrites the trigger, which exists with the same name.

{ BEFORE | AFTER | INSTEAD OF }:

BEFORE or AFTER statement in the syntax indicates at what time the trigger is fired on the table.

For example, users can use Trigger Before or After inserting data into the table, Before or After updating the data of the table, Before or After deleting the data from the table. INSTEAD OF is a statement, which is used for creating the Trigger on the view. 

{ INSERT [ OR ] | UPDATE [ OR ] | DELETE }:

This statement in the syntax specifies the DML operations. 

[OF col_name] :

This statement in the syntax is only used when the update operation is triggered.

ON table_name  :

This statement in the syntax specifies the table name on which DML operation is to be applied.

REFERENCING [ OLD AS old | NEW AS new ]  :

It is a statement that allows the user to provide the new value by replacing the old value. By default, we have to reference the values in the following form:

:old.column_name

:new.column_name

[ FOR EACH ROW  ]:

This statement in the syntax specifies the row-level triggered.

WHEN (condition):

It is a clause that specifies the condition to be applied to the database data. It is triggered only for those rows which satisfy the condition.

Declare, Begin Body :

Both statements contain the queries and SQL statements to be executed when the trigger is called.

Types of Trigger

The trigger can be categorized into the following three types:

1. Statement Level Trigger

2. Row Level Trigger

3. Before Trigger

4. After Trigger

Row Level Trigger

Row-level Trigger is that trigger that executes once for each table row. This type of trigger always uses the FOR EACH ROW clause in the trigger procedure.

Statement Level Trigger

Statement Level Trigger is that trigger that executes only one time for each statement. In this type of trigger, FOR EACH ROW clause is not present, i.e., it is omitted.

Before Trigger

'Before Trigger' is called before the execution of the DML operations on the database table.

After Trigger

'After Trigger' is called or fired after the execution of DML operations on the database table.