Active Databases And Triggers
Seems Like a complex topic?
But not that !! Let Me Unfold it in a simpler way :)
Introduction
A trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to the database. The database with a set of associated triggers is called Active Database.
Triggers
The trigger is a set of SQL statements that live in system memory with unique names. PL/SQL code is executed automatically in response to a database event, typically DML. Like other stored procedures, triggers are stored in the database. Often used to enforce complex constraints, especially multi-table constraints. It is a specialized category of stored procedure that is called automatically when a database server event occurs. Each trigger is always associated with a table. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. While a procedure is explicitly executed by a user, application, or trigger, one or more triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter which user is connected or which application is being used.
Notice that triggers are stored in the database separately from their associated tables.
Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
Triggers work in ECA Model. It has mainly three parts,
Event: A change to a database that activates the trigger
Condition: A trigger restriction specifies a boolean expression must be TRUE for the trigger to fire
Action: A procedure that is executed when the trigger is activated. Similar to the stored procedure it contains PL/SQL statement as we discussed earlier.
Types of Trigger as per SQL Standards
- Row-level triggers
- Statement-level triggers
Row Level Triggers
- Row-level triggers execute once for each row in a transaction.
- Row-level triggers are the most common type of triggers; they are often used in data auditing applications.
- These are identified by the FOR EACH ROW clause in the CREATE TRIGGER command.
- Check individual values and can update them.
- They Have access to new and: old vectors.
Statement-level Triggers
- Statement-level triggers for transaction-related activities
- Statement-level triggers execute once for each transaction.
- For example, if a single transaction inserted 500 rows into the Customer table, then a statement-level trigger on that table would only be executed once.
- Do not have access to new or: old vectors (Distinguishing point)
- Execute once for the entire statement regardless of how many
records are affected - Used for verification before or after the statement
Order of Trigger Firing
Types of Triggers based on Operations
- Data Definition Language (DDL) Triggers
- Data Manipulation Language (DML) Triggers
- Logon Triggers
DDL Triggers
As The Name suggests, DDL Triggers are related to DDL commands. DDL triggers respond to DDL events like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. For example, you can define a DDL trigger that records CREATE or ALTER TABLE operations.
We can create these triggers at the database level or server level, depending on the type of DDL events. It can also be executed in response to certain system-defined stored procedures that do DDL-like operations.
The DDL triggers are useful in the following scenario:
- Need to prevent Database schema from changing
- To respond to any changes in schema
- Audit changes made in the database schema
DDL Trigger Syntax
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR { event_type | event_group }
AS
{sql_statement}DML Trigger
DML triggers are fired in response to DML events like INSERT, UPDATE, and DELETE statements in the user’s table or view. It can also be executed in response to DML-like operations performed by system-defined stored procedures.
Broadly DML Triggers can be categorized into two types:
a. After Triggers
b. Instead of Triggers
AFTER Triggers
After trigger fires, when SQL Server completes the triggering action successfully, that fired it. Generally, this trigger is executed when a table completes an insert, update or delete operations. It is not supported in views. Sometimes it is known as FOR triggers.
We can classify AFTER Triggers into three types based on operation:
- AFTER INSERT Trigger
- AFTER UPDATE Trigger
- AFTER DELETE Trigger
When we insert data into a table, the trigger associated with the insert operation on that table will not fire until the row has passed all constraints, such as the primary key constraint. SQL Server cannot fire the AFTER trigger when the data insertion failed.
AFTER Trigger Syntax
CREATE TRIGGER schema_name.trigger_name
ON table_name
AFTER {INSERT | UPDATE | DELETE}
AS
BEGIN
— Trigger Statements
— Insert, Update, Or Delete Statements
ENDInstead of Triggers
Instead of Trigger fires before SQL Server begins to execute the triggering operation that triggered it. It means that no condition constraint check is needed before the trigger runs. As a result, even if the constraint check fails, this trigger will fire. It is the opposite of the AFTER trigger. We can create the INSTEAD OF triggers on a table that executes successfully but doesn’t contain the table’s actual insert, update, or delete operations.
We can classify Instead of Triggers into three types based on operation:
- Instead of INSERT Trigger
- Instead of UPDATE Trigger
- Instead of DELETE Trigger
When we insert data into a table, the trigger associated with the insert operation on that table will fire before the data has passed all constraints, such as the primary key constraint. SQL Server also fires the Instead of Trigger if the data insertion fails.
CREATE TRIGGER schema_name.trigger_name
ON table_name
INSTEAD OF {INSERT | UPDATE | DELETE}
AS
BEGIN
— trigger statements
— Insert, Update, or Delete commands
ENDLogon Trigger
Logon triggers are fired in response to a LOGON event. The LOGON event occurs when a user session is generated with an SQL Server instance, which is made after the authentication process of logging is completed but before establishing a user session. As a result, the SQL Server error log will display all messages created by the trigger, including error messages and the PRINT statement messages. If authentication fails, logon triggers do not execute. These triggers may be used to audit and control server sessions, such as tracking login activity or limiting the number of sessions for a particular login.
Before removing further to active databases, first of all, congratulations for reading until here bcoz that requires a hell amount of concentration, kudos !!
Just Like Active Passive Voice in our English grammar, we have an active database and passive database as well.
Passive Database
Traditional database systems are passive in the sense that commands are executed by the database as and when requested by an application program. It has passive update principle i.e. client controls DBMS updates. However, some situations can’t be effectively tackled by this model.
Active Database
The active database supports the preceding application by moving the reactive behavior from the application into the DBMS. Active databases are thus able to monitor and react to specific circumstances of relevance to an application. An active database system must provide a knowledge model i.e. description mechanism and an execution model for supporting this behavior. And for this we need triggers which has predefined action when situations occur.
An active Database is a database consisting of a set of triggers. These databases are very difficult to be maintained because of the complexity that arises in understanding the effect of these triggers. In such a database, DBMS initially verifies whether the particular trigger specified in the statement that modifies the database) is activated or not, prior to executing the statement.
If the trigger is active then DBMS executes the condition part and then executes the action part only if the specified condition is evaluated to true. It is possible to activate more than one trigger within a single statement.
In such a situation, DBMS processes each of the triggers randomly. The execution of an active part of a trigger may either activate other triggers or the same trigger that Initialized this action. Such types of triggers that activate themselves are called ‘recursive triggers’. The DBMS executes such chains of triggers in some pre-defined manner but it affects the concept of understanding.
Features of Active Database
- It possesses all the concepts of a conventional database i.e. data modeling facilities, query language, etc.
- It supports all the functions of a traditional database like data definition, data manipulation, storage management, etc.
- It supports the definition and management of ECA rules.
- It detects event occurrence.
- It must be able to evaluate conditions and execute actions.
- It means that it has to implement rule execution.
Advantages of Active Database
- It Enhances traditional database functionalities with powerful rule processing capabilities.
- Enable a uniform and centralized description of the business rules relevant to the information system.
- Avoids redundancy of checking and repair operations.
- A suitable platform for building a large and efficient knowledge base and expert systems.
Thanks for reading this article !!
If I got something wrong? Let me know in the comments. I would love to improve.
Clap 👏 If this article helps you and if you would like to read my blogs, please follow me on medium.
