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.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

The active database is the one that is currently being used by the clients that have mailboxes in that database. All the transactions for that database are being generated by the server it’s on.

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.

A trigger is designed to check or change data based on a data modification or definition statement; it shouldn’t return data to the user. The Transact-SQL statements in a trigger frequently include control-of-flow language. SELECT * FROM deleted; For more information, see Use the inserted and deleted Tables.

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:

Explain about triggers and active databases in DBMS

Active Databases - GeeksforGeeks

Temporal triggers in active databases

https://www.researchgate.net/publication/220566764_Active_Database_Systems