Active database and Triggers

Active Database

An active database is a database that includes an event-driven architecture (often in the form of ECA rules) which can respond to conditions both inside and outside the database. Possible uses include security monitoring, alerting, statistics gathering and authorization.

Features of Active Database:

  1. It possesses all the concepts of a conventional database i.e. data modeling facilities, query language etc.
  2. It supports all the functions of a traditional database like data definition, data manipulation, storage management etc.
  3. It supports definition and management of ECA rules.
  4. It detects event occurrences.
  5. It must be able to evaluate conditions and to execute actions.
  6. It means that it has to implement rule execution.

Advantages:

  1. Enhances traditional database functionalities with powerful rule processing capabilities.
  2. Enable a uniform and centralized description of the business rules relevant to the information system.
  3. Avoids redundancy of checking and repair operations.
  4. Suitable platform for building large and efficient knowledge base and expert systems.

Triggers

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

Why do we need Triggers ?

Tigger resides in the database and anyone who has the required privilege can use it, a trigger lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation.

Types of Triggers

  • Row level trigger: — Row level trigger is executed when each row of the table is inserted/ updated/ deleted. If it is a row level trigger, then we have to explicitly specify while creating the trigger, as we did in the above example. Also, we have to specify the WHEN (condition) in the trigger.
  • Statement level trigger: — this trigger will be executed only once for DML statements. This DML statement may insert / delete/ update one row or multiple rows or whole table. Irrespective of the number of rows, this trigger will be fired for the statement. If we have not specified the type of trigger while creating, by default it would be a statement level trigger.

Conclusion:

Triggers offer benefits to your business. These benefits are: Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application. Triggers are similar to stored procedures

References:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store