Explain triggers and assertions and explain it with through appropriate query.

Introduction to Triggers

Definition of Triggers

Triggers in a Database Management System (DBMS) are a type of stored procedure that automatically execute or fire when a specified event occurs within the database. They are special instructions that are activated when certain actions such as INSERT, UPDATE, or DELETE are performed on a table.

Purpose and use of Triggers in DBMS

Triggers are used to maintain the integrity of the data in the database. They can be used to enforce business rules and to maintain complex integrity constraints which cannot be enforced using the standard integrity checking mechanisms of the DBMS.

Types of Triggers

Triggers can be classified into two types:

1. Row-level Triggers: These triggers are fired whenever a row of a table is affected by the triggering event.

2. Statement-level Triggers: These triggers are fired whenever a statement affects the table.

Syntax of creating a Trigger

The basic syntax for creating a trigger in SQL is as follows:

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name FOR EACH ROW 
trigger_body;

Example of a Trigger

Let's consider an example where we have a table EMPLOYEES and we want to create a trigger that will insert a row into the AUDIT_LOG table whenever a row in the EMPLOYEES table is deleted.

CREATE TRIGGER before_employee_delete 
BEFORE DELETE 
ON EMPLOYEES FOR EACH ROW 
BEGIN 
   INSERT INTO AUDIT_LOG(old_id, old_name, action) 
   VALUES (OLD.id, OLD.name, 'delete'); 
END;

In this example, before_employee_delete is the trigger which will be activated before a delete operation on EMPLOYEES table. It will insert the old values of id and name into the AUDIT_LOG table along with the action 'delete'.

Introduction to Assertions

Definition of Assertions

Assertions in DBMS are a predicate expressing a condition that we wish the database to always satisfy. They are used to specify a condition that always holds true for the entire database.

Purpose and use of Assertions in DBMS

Assertions are used to enforce complex integrity constraints that cannot be enforced by other integrity rules of DBMS.

Syntax of creating an Assertion

The basic syntax for creating an assertion in SQL is as follows:

CREATE ASSERTION assertion_name 
CHECK (condition);

Example of an Assertion

Let's consider an example where we have a table EMPLOYEES and we want to create an assertion that the salary of an employee should not be more than 50000.

CREATE ASSERTION salary_check 
CHECK (NOT EXISTS (SELECT * FROM EMPLOYEES WHERE salary > 50000));

In this example, salary_check is the assertion which will check that no employee has a salary more than 50000.

Differences between Triggers and Assertions

Conclusion

Triggers and assertions are powerful tools in DBMS used to maintain the integrity of the data. While triggers are used to automate certain operations in the database, assertions are used to enforce complex integrity constraints on the database.

References

1. Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Addison-Wesley.
2. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill.

Summary

Triggers and assertions are important tools in a Database Management System (DBMS) that help maintain data integrity. Triggers are special instructions that automatically execute when certain events occur, such as INSERT, UPDATE, or DELETE operations on a table. They are used to enforce business rules and maintain complex integrity constraints. Assertions, on the other hand, are predicates that express conditions that the database should always satisfy. They are used to specify conditions that must always hold true for the entire database. Triggers are associated with tables and can modify other tables, while assertions apply to the entire database and cannot modify other tables. Triggers can be activated before or after an event, while assertions are checked whenever the database changes. Both triggers and assertions play a crucial role in maintaining data integrity in a DBMS.

Analogy

Triggers and assertions in a DBMS are like automated systems and security checks in a building. Triggers are like automated systems that are activated when certain events occur, such as opening a door or entering a restricted area. They perform specific actions, such as sounding an alarm or locking the door, to ensure the security and integrity of the building. Assertions, on the other hand, are like security checks that are always in place to ensure that everyone entering the building follows the rules and regulations. They enforce conditions, such as wearing a security badge or presenting identification, to maintain the overall security and integrity of the building. Just as triggers and assertions work together to maintain the security and integrity of a building, they also work together in a DBMS to maintain the integrity of the data.