Demonstrate working of Assertion & Triggers in databases with an example.

Demonstrate working of Assertion & Triggers in databases with an example.

Assertions

Assertions are used to specify additional types of constraints beyond the built-in relational model constraints. In SQL, users can declare these constraints using the CREATE ASSERTION statement in the Data Definition Language (DDL). Each assertion is given a name and is defined by a condition similar to the WHERE clause of an SQL query.

General form:

CREATE ASSERTION <Name_of_assertion> CHECK (<condition>)

For the assertion to be satisfied, the condition specified after the CHECK clause must return true. For example, to enforce the constraint that an employee’s salary must not be greater than the salary of the manager of the department they work for, you can use the following assertion:

CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
    NOT EXISTS (
        SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
        WHERE E.Salary > M.Salary
        AND E.Dno = D.Dnumber
        AND D.Mgr_ssn = M.Ssn
    )
);

In this example, SALARY_CONSTRAINT is the name of the constraint, followed by the CHECK keyword and a condition in parentheses. The condition must hold true in every database state for the assertion to be satisfied. The NOT EXISTS clause ensures that the result of the query is empty, indicating that the condition is always true. If the result is not empty, the assertion is violated.

Triggers

A trigger is a procedure that automatically runs when a specific event occurs in the DBMS. It’s useful for specifying actions to be taken when certain events happen and conditions are met. The CREATE TRIGGER statement is used to define these actions in SQL.

General form:

CREATE TRIGGER <name>
BEFORE | AFTER | <events>
FOR EACH ROW | FOR EACH STATEMENT
WHEN (<condition>)
<action>

A trigger has three main components:

1. Event: The trigger is activated when this event occurs. Events can be Insert, Update, or Delete and it has 2 triggering times: Before the event, After the event
2. Condition (optional): If the condition is true, the trigger executes; otherwise, it’s skipped.
3. Action: The actions performed by the trigger when the event occurs and the condition is true.