Specifying Constraints as Assertions and Actions as Triggers

This section introduces two advanced SQL features:

1. Assertions → used to enforce general constraints

2. Triggers → used to perform automatic actions when events occur

These features go beyond basic constraints like:

• Primary key

• Unique

• NOT NULL

• Foreign key

---

Part 1: CREATE ASSERTION (General Constraints)

---

1. Why Do We Need Assertions?

SQL already supports constraints like:

• PRIMARY KEY

• UNIQUE

• FOREIGN KEY

• CHECK

But some rules cannot be enforced on a single table or attribute.

📌 Example of a complex rule:

An employee’s salary must not exceed the salary of their department manager.

This rule:

• Involves multiple tables

• Compares multiple tuples

• Cannot be expressed using simple CHECK inside CREATE TABLE

👉 This is where ASSERTIONS are used.

---

2. What Is an Assertion?

An assertion is:

• A global constraint

• Applied to the entire database

• Checked whenever the database changes

SQL Syntax

CREATE ASSERTION assertion_name
CHECK (condition);

• assertion_name: name of the constraint

• condition: must always evaluate to TRUE

If it ever becomes FALSE, the database rejects the update.

---

3. Example: Salary Constraint Assertion

Problem Rule

No employee should earn more than their department manager.

Assertion Code

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
  )
);

---

4. How This Assertion Works (Step-by-Step)

Step 1: Inner Query

SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.Salary > M.Salary
  AND E.Dno = D.Dnumber
  AND D.Mgr_ssn = M.Ssn

This query finds:

• Employees E

• Their managers M

• Where employee salary > manager salary

👉 These are violations.

---

Step 2: NOT EXISTS

NOT EXISTS (violating tuples)

• If no violating tuples exist, condition is TRUE

• If any violating tuple exists, condition becomes FALSE

📌 The assertion requires the result to be empty at all times

---

5. When Is an Assertion Violated?

• When any update causes the query to return at least one row

• Examples:

  • Increase employee salary

  • Decrease manager salary

  • Change department manager

  • Insert new employee

🚫 The DBMS rejects the operation if the assertion is violated.

---

6. Assertions vs CHECK Constraints

Feature	CHECK (Table/Attribute)	CREATE ASSERTION
Scope	Single row / table	Whole database
Performance	Efficient	Expensive
Multi-table constraint	❌ No	✅ Yes
Checked on	Insert / Update only	Any DB change

📌 Design Rule:

• Use CHECK whenever possible

• Use CREATE ASSERTION only when necessary

⚠️ Many commercial DBMSs do not fully support assertions because they are expensive to enforce.

---

Part 2: CREATE TRIGGER (Active Databases)

---

7. What Is a Trigger?

A trigger is a rule that tells the database:

When something happens, and a condition is true, do something automatically.

This makes the database active, not passive.

---

8. When Are Triggers Used?

Triggers are used for:

• Enforcing complex constraints

• Monitoring updates

• Sending alerts

• Maintaining derived data

• Logging changes

---

9. Trigger Structure: ECA Rule

Triggers follow the ECA model:

Component	Meaning
Event	What causes the trigger
Condition	Optional test
Action	What to do if condition is true

---

10. Example Trigger: Salary Violation

Problem Rule

If an employee’s salary becomes greater than their supervisor’s salary, notify the supervisor.

---

Trigger Code (Oracle Syntax)

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN
ON EMPLOYEE
FOR EACH ROW
WHEN (
  NEW.SALARY >
    ( SELECT SALARY
      FROM EMPLOYEE
      WHERE SSN = NEW.SUPERVISOR_SSN )
)
INFORM_SUPERVISOR(
  NEW.Supervisor_ssn,
  NEW.Ssn
);

---

11. Trigger Explanation (Line by Line)

Trigger Name

CREATE TRIGGER SALARY_VIOLATION

• Used to enable, disable, or drop the trigger later

---

Event

BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN
ON EMPLOYEE

Trigger fires when:

• A new employee is inserted

• Salary is updated

• Supervisor is changed

📌 BEFORE → trigger runs before the change is applied

---

Row-Level Trigger

FOR EACH ROW

• Trigger executes once per affected row

• Uses NEW and OLD values

---

Condition

WHEN ( NEW.SALARY > ... )

• Checked after event occurs

• If FALSE → no action

• If TRUE → action executed

---

Action

INFORM_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn);

• Calls a stored procedure

• Sends notification to supervisor

---

12. BEFORE vs AFTER Triggers

Type	Purpose
BEFORE	Prevent invalid updates
AFTER	Logging, notifications

---

13. Assertions vs Triggers (Key Differences)

Aspect	Assertion	Trigger
Purpose	Enforce constraint	Perform action
Automatic action	❌ No	✅ Yes
Scope	Whole database	Table-specific
Complexity	Declarative	Procedural
Support	Limited	Widely supported

📌 In practice:

• Assertions are rarely implemented

• Triggers are widely used

---

14. When to Use What?

• Use CHECK → simple constraints

• Use ASSERTION → global logical rules

• Use TRIGGER → automatic actions or complex enforcement

---

Final Takeaway 

• Assertions ensure database correctness

• Triggers make the database reactive

• Both go beyond traditional relational constraints

• Triggers follow the ECA model