SQL TRIGGERS

 SQL Triggers

A Trigger is a special type of stored program in a database that automatically executes when a specific event occurs on a table or view. Triggers are mainly used to maintain data integrity, enforce business rules, and automate certain actions.

Triggers are commonly supported in relational database systems like Oracle Database, MySQL, and Microsoft SQL Server.

🔹 When Does a Trigger Execute?

A trigger is executed automatically during events such as:

  • INSERT – When a new record is added

  • UPDATE – When an existing record is modified

  • DELETE – When a record is removed

🔹 Types of Triggers

  • BEFORE Trigger – Executes before the event occurs

  • AFTER Trigger – Executes after the event occurs

  • INSTEAD OF Trigger – Executes instead of the actual operation (commonly used with views)

🔹 Uses of Triggers

  • Enforcing business rules

  • Maintaining audit logs

  • Validating data before insertion or update

  • Automatically updating related tables

SQL triggers help automate database operations and ensure consistency without requiring manual intervention. 


click here for more information

Comments

Post a Comment

Popular posts from this blog

Dependency Preservation

Dependency Preservation is an important concept in database normalization that ensures all functional dependencies of a relation remain valid even after the relation is decomposed into smaller tables. When a large table is divided to reduce redundancy and improve design, it is necessary to make sure that the original constraints and rules between attributes are not lost. If all functional dependencies can be enforced directly on the decomposed tables without performing a join operation, the decomposition is said to be dependency preserving. This helps maintain data integrity, reduces complexity, and improves efficiency in database management. A good database design aims to achieve both lossless decomposition and dependency preservation to ensure accuracy and consistency of data.  Click Here  for more information.
Read more

NORMALIZATION

 Normalization is a systematic process used in relational database design to organize data efficiently and reduce redundancy. The main goal of normalization is to eliminate unnecessary duplication of data, avoid update anomalies, and ensure data integrity. It divides large tables into smaller, well-structured tables while maintaining relationships between them. Normalization is achieved through a series of normal forms, each with specific rules: First Normal Form (1NF) ensures that all attributes contain atomic (single) values and there are no repeating groups. Second Normal Form (2NF) removes partial dependency, meaning non-key attributes must depend on the entire primary key. Third Normal Form (3NF) removes transitive dependency, ensuring non-key attributes depend only on the primary key. Boyce-Codd Normal Form (BCNF) is a stronger version of 3NF that handles certain anomalies not covered in 3NF. Fourth Normal Form (4NF) deals with multivalued dependencies. ...
Read more

Boyce-Codd Normal Form (BCNF)

 Boyce-Codd Normal Form (BCNF) Boyce-Codd Normal Form (BCNF) is an advanced level of normalization in relational database design. It is a stronger version of Third Normal Form (3NF) and is used to eliminate certain types of redundancy and anomalies that 3NF may not fully resolve. A relation is said to be in BCNF if, for every non-trivial functional dependency A → B , attribute A must be a super key of the relation. 🔹 In Simple Terms If a determinant (left side of a functional dependency) is not a super key, then the table violates BCNF. 🔹 Why BCNF is Needed Sometimes, a table may satisfy 3NF but still have redundancy due to overlapping candidate keys. BCNF removes such problems by ensuring that only super keys determine other attributes. 🔹 Example Consider a relation: R(Student, Subject, Teacher) Functional Dependencies: (Student, Subject) → Teacher Teacher → Subject Here, Teacher → Subject violates BCNF because Teacher is not a super key. To satisfy BC...
Read more