CIR 303 : ADVANCED DATABASE SYSTEM

COMPUTER SCIENCE

TRIGGERS & ACTIVE DB

 OVERVIEW
OVERVIEW

What is trigger?

General format of trigger.

Active databases

2
 TRIGGER
WHAT IS TRIGGER?

A trigger is a set of instructions (or stored procedure) that

are automatically executed by a database in response to
events in a particular table or view.

3
 TRIGGER
WHAT IS TRIGGER?
General format of the trigger include:
●
Event – Something happens (e.g. data inserted,
updated or deleted)
●
Condition – A logical test is checked
●
Action – If the condition is true, the database executes
predefined operations
4
 TRIGGER
SYNTAX TO CREATE TRIGGER
Create trigger Trigger_name
(before | after) [insert | update | delete]
on [table_name]
[for each row]
[trigger_body];

5
 TRIGGER
SYNTAX TO CREATE TRIGGER
CREATE TRIGGER:
Specify that a triggered block is going to be declared.

TRIGGER_NAME:
Creates or replaces an existing trigger with the
Trigger_name. The trigger name should be unique
6
 TRIGGER
SYNTAX TO CREATE TRIGGER
BEFORE | AFTER:
Specifies when the trigger will be initiated i.e before the
ongoing event or after the ongoing event

7
 TRIGGER
SYNTAX TO CREATE TRIGGER
Example – table:
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);

8
 TRIGGER
SYNTAX TO CREATE TRIGGER
Example – trigger:
CREATE TRIGGER check_salary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END;

9
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Automatic execution:

Triggers fire automatically in response to a specific event

(e.g. INSERT, UPDATE, DELETE) without explicit calls
from the user or application

10
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Event-driven:

Triggers are always tied to an event on a table or view

(e.g. before inserting a row, after updating a column)
●
PREDEFINED LOGIC: The logic inside a trigger is
written using SQL
11
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Timing - Triggers can run:
BEFORE the event (to validate or modify data)
AFTER the event (to take action based on the
change)
…..
12
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Timing - Triggers can run:
INSTEAD OF the event (often used on views to
define custom behaviour)

13
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Bound to Tables or Views:

Triggers are associated with a specific table or view

●
Granularity:

…………..

14
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Granularity:

Row level – executes once for each affected row

Statement level – executes once per triggering SQL

statement, regardless of how many rows are affected

15
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Persistence:
Once created, triggers are stored in the database
schema and remain active until explicitly dropped or
disabled.
●
Transparency:
Triggers work behind the scenes; applications using the
database do not need to be aware of them.
16
 TRIGGER
KEY ASPECTS OF TRIGGERS
●
Performance Consideration:
Because they run automatically and invisibly, poorly
designed triggers can slow down database operations or
create unintended side effects (like recursive firing).

17
 TRIGGER
USES OF TRIGGERS
●
Maintaining data integrity:
Enforce complex rules that constraints (like CHECK or
FOREIGN KEY) can’t cover.
Example: Prevent inserting a negative salary value or
ensure age is above a threshold.

18
 TRIGGER
USES OF TRIGGERS
●
Cascading Actions:
Automatically propagate changes across related tables.
Example: If an order is deleted, remove its order items
from the Order Details table.

19
 TRIGGER
USES OF TRIGGERS
●
Auditing and Logging:
Track changes made to sensitive data.
Example: Keep a history table of who updated employee
salaries and when.

20
 TRIGGER
USES OF TRIGGERS
●
Enforcing Business Rules:
Implement custom logic at the database level.
Example: Automatically set a default status of "Pending"
when a new order is inserted.

21
 TRIGGER
USES OF TRIGGERS
●
Preventing Invalid Transactions:
Roll back changes if conditions are not met.
Example: Block deletion of a customer if they still have
unpaid invoices.

22
 TRIGGER
USES OF TRIGGERS
●
Derived or Calculated Values:
Automatically compute and update values in related
tables.
Example: Update account balance after every deposit or
withdrawal.

23
 TRIGGER
USES OF TRIGGERS
●
Synchronising Tables:
Keep duplicate or related data consistent across multiple
tables.
Example: Update a summary table whenever detailed
records change.

24
 TRIGGER
USES OF TRIGGERS
●
Security Control:
Restrict unauthorised operations by monitoring and
reacting to changes.
Example: Deny updates to certain fields if the user
doesn’t have permission.

25
 TRIGGER
USES OF TRIGGERS
In summary, triggers ensure automation, consistency
and reliability inside the database itself without
depending on the application code

26
 ACTIVE DATABASE
DEFINITION
Active Database:
A type of database that can automatically respond to
events and conditions without requiring explicit requests
from the user or application
i.e. a database consisting of a set of triggers thus triggers
are the mechanism that make a database “active”

27
 ACTIVE VS PASSIVE DATABASE
DEFINITION
Active vs Passive Database:
Passive database → Just stores and retrieves data. It only
acts when explicitly queried by a user/application.
Active database → Uses triggers (and ECA rules) to
automatically react to events.

28
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

Stores and
Responds automatically
retrieves data only
Nature to events and
when explicitly
conditions.
requested.

29
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

User or application
Database can act on its
User must issue
own once rules/triggers
Involvement queries/commands
are defined.
for every action.

30
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

Standard SQL
operations Uses triggers and ECA
Mechanism (SELECT, INSERT, rules (Event–Condition–
UPDATE, Action).
DELETE).

31
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

No built-in High automation;

automation; actions can occur
Automation
everything is without user
manual. intervention.

32
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

Updating stock
A trigger fires
requires an
automatically after an
Example application to
order insert to reduce
check stock levels
stock.
and adjust.

33
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

Must be enforced
Can be enforced
Integrity & at the application
dynamically through
Rules level or with
triggers.
constraints.

34
 ACTIVE VS PASSIVE DATABASE
DEFINITION

ASPECT PASSIVE DB ACTIVE DB

Business rules
Data storage and enforcement, auditing,
Use Cases
retrieval. alerts, real-time
monitoring.

35
 ACTIVE DATABASE
EXAMPLE
In an active database, if a new order is inserted with a
quantity greater than stock available, the system can:
●
Automatically reject the order (condition fails), or
●
Automatically send a restock request (action executed).

36
 ACTIVE DATABASE
EXAMPLE
CREATE TRIGGER update_stock
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Products
SET stock = stock - NEW.quantity
WHERE id = NEW.product_id;
END;

37
 ACTIVE DATABASE
EXAMPLE
Event → New order inserted.
Condition → Always runs (can add stock checks if needed).
Action → Automatically reduces product stock.

38
 ACTIVE DATABASE
SUMMARY
An active database is a self-monitoring, self-acting
database that reacts automatically to certain events by
executing rules (commonly through triggers).
Active databases rely heavily on triggers to implement
ECA.

39
 ACTIVE DATABASE
ADVANTAGES
●
Automation – Actions are executed automatically via
triggers (less manual intervention).
●
Data Integrity – Enforces rules consistently inside the
database.
●
Consistency – Business rules apply across all
applications using the DB.

40
 ACTIVE DATABASE
ADVANTAGES
●
Auditing & Logging – Easy to track changes
automatically.
●
Real-time Response – Immediate reaction to events
(alerts, cascades, validations).
●
Reduced Application Logic – Shifts repetitive
checks/work from applications into the DB.

41
 ACTIVE DATABASE
ADVANTAGES
●
Security & Control – Can prevent unauthorised or invalid
operations at the DB level.

42
 ACTIVE DATABASE
DISADVANTAGES
●
Complexity – Harder to design, understand, and debug
triggers.
●
Hidden Logic – Behaviour happens “behind the scenes”
→ developers may not expect it.
●
Performance Overhead – Extra processing slows down
inserts/updates/deletes if triggers are heavy.

43
 ACTIVE DATABASE
DISADVANTAGES
●
Portability Issues – Trigger syntax differs across DBMS
(Postgres, MySQL, Oracle, SQL Server).
●
Maintenance Burden – Multiple interdependent triggers
may cause recursion or conflicts.
●
Limited Scope – Best for small, rule-based logic;
unsuitable for very complex business processes.

44
 ACTIVE DATABASE
DISADVANTAGES
●
Risk of Unintended Effects – Poorly designed triggers
may fire recursively or cause unexpected results.

45
THANK YOU

46