Assignment 5: Triggers.
SET A
Movie – Actor Database
Consider the following database
Movie (m_name varchar (25), release_year integer, budget money)
Actor (a_name varchar(30), role varchar(30), charges money, a_address varchar(30) ) Movie
and Actor are related with many to many relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger which will be executed whenever an actor is deleted from the actor table, display
appropriate message.
b. Write a trigger which will be executed whenever a movie is deleted from the movie table, display
appropriate message.
c. Write a trigger which will be executed whenever insertion is made to the movie table. If the
budget is less than 1,00,000 do not allow the insertion. Give appropriate message.
-- Movie Table
CREATE TABLE Movie (
m_name VARCHAR(25) PRIMARY KEY,
release_year INT,
budget MONEY
);
-- Actor Table
CREATE TABLE Actor (
a_name VARCHAR(30) PRIMARY KEY,
role VARCHAR(30),
charges MONEY,
a_address VARCHAR(30)
);
-- Junction Table for Many-to-Many Relationship
CREATE TABLE Movie_Actor (
m_name VARCHAR(25),
a_name VARCHAR(30),
PRIMARY KEY (m_name, a_name),
FOREIGN KEY (m_name) REFERENCES Movie(m_name),
FOREIGN KEY (a_name) REFERENCES Actor(a_name)
);
2. Insert Sample Records
sql
-- Insert into Movie
INSERT INTO Movie VALUES
('Inception', 2010, 160000000),
('Titanic', 1997, 200000000),
('The Matrix', 1999, 63000000);
-- Insert into Actor
INSERT INTO Actor VALUES
('Leonardo DiCaprio', 'Lead', 20000000, 'LA'),
('Kate Winslet', 'Lead', 15000000, 'UK'),
('Keanu Reeves', 'Lead', 10000000, 'Canada');
-- Insert into Movie_Actor
INSERT INTO Movie_Actor VALUES
('Inception', 'Leonardo DiCaprio'),
('Titanic', 'Leonardo DiCaprio'),
('Titanic', 'Kate Winslet'),
('The Matrix', 'Keanu Reeves');
3. Triggers
a) Trigger on Actor deletion Display message whenever an actor is deleted
sql
-- Function for Actor deletion
CREATE OR REPLACE FUNCTION actor_delete_msg()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Actor "%" is deleted from Actor table', OLD.a_name;
RETURN OLD;
END;
$$;
-- Trigger on DELETE
CREATE TRIGGER trg_actor_delete
AFTER DELETE ON Actor
FOR EACH ROW
EXECUTE FUNCTION actor_delete_msg();
Test
sql
DELETE FROM Actor WHERE a_name = 'Keanu Reeves';
-- NOTICE: Actor "Keanu Reeves" is deleted from Actor table
b) Trigger on Movie deletion Display message whenever a movie is deleted
sql
-- Function for Movie deletion
CREATE OR REPLACE FUNCTION movie_delete_msg()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Movie "%" is deleted from Movie table', OLD.m_name;
RETURN OLD;
END;
$$;
-- Trigger on DELETE
CREATE TRIGGER trg_movie_delete
AFTER DELETE ON Movie
FOR EACH ROW
EXECUTE FUNCTION movie_delete_msg();
Test
sql
DELETE FROM Movie WHERE m_name = 'Titanic';
-- NOTICE: Movie "Titanic" is deleted from Movie table
c) Trigger on Movie insertion to check budget Prevent insertion if budget < 100,000
sql
-- Function for Movie insertion
CREATE OR REPLACE FUNCTION check_movie_budget()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.budget < 100000 THEN
RAISE EXCEPTION 'Insertion not allowed. Budget less than 1,00,000: %', NEW.budget;
END IF;
RETURN NEW;
END;
$$;
-- Trigger on INSERT
CREATE TRIGGER trg_movie_insert
BEFORE INSERT ON Movie
FOR EACH ROW
EXECUTE FUNCTION check_movie_budget();
Test
sql
-- Valid insertion
INSERT INTO Movie VALUES ('Avatar', 2009, 237000000); -- Works fine
-- Invalid insertion
INSERT INTO Movie VALUES ('LowBudgetMovie', 2026, 50000);
-- ERROR: Insertion not allowed. Budget less than 1,00,000: 50000
SET B
Doctor – Hospital Database
Consider the following database
Doctor (d_no int, d_name varchar(30), specialization varchar(35), charges int)
Hospital (h_no int, h_name varchar(20), city varchar(10))
Doctor and Hospital are related with many to one relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger before insert/update on Doctor table. Raise exception if charges are <0.
b. Write a trigger that restricts insertion of charges value greater than 400.
-- Create Hospital table
CREATE TABLE Hospital (
h_no int PRIMARY KEY,
h_name varchar(20),
city varchar(10)
);
-- Create Doctor table (Many-to-One: Many doctors belong to one hospital)
CREATE TABLE Doctor (
d_no int PRIMARY KEY,
d_name varchar(30),
specialization varchar(35),
charges int,
h_no int REFERENCES Hospital(h_no)
);
-- Insert sample records
INSERT INTO Hospital VALUES (1, 'City Care', 'Mumbai'), (2, 'Life Line', 'Pune');
INSERT INTO Doctor VALUES (101, 'Dr. Sharma', 'Cardiology', 500, 1);
CREATE OR REPLACE FUNCTION validate_doctor_charges()
RETURNS TRIGGER AS $$
BEGIN
-- a. Check if charges are less than 0
IF NEW.charges < 0 THEN
RAISE EXCEPTION 'Invalid Charges: Charges cannot be negative.';
END IF;
-- b. Restrict insertion of charges greater than 400
IF NEW.charges > 400 THEN
RAISE EXCEPTION 'Invalid Charges: Charges cannot exceed 400.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Create the Trigger
CREATE TRIGGER trg_check_charges
BEFORE INSERT OR UPDATE ON Doctor
FOR EACH ROW
EXECUTE FUNCTION validate_doctor_charges();
SET C
Student – Subject database
Consider the following database :
Student (rollno integer, name varchar(30),city varchar(50),class varchar(10))
Subject(Scode varchar(10),subject name varchar(20))
Student and subject are related with M-M relationship with attributes marks_scored.
Create the above database in PostGreSQL and insert sufficient records
a. Write a trigger before insert/update the marks_scored. Raise exception if Marks are negative.
b. Write a trigger which is executed when insertion is made in the student-subject table. If
marks_scored is less than 0, give appropriate message and do not allow the insertion.
c. Write a trigger which will prevent deleting students from ‘Mumbai’ city.
-- Student Table
CREATE TABLE Student (
rollno INT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(50),
class VARCHAR(10)
);
-- Subject Table
CREATE TABLE Subject (
scode VARCHAR(10) PRIMARY KEY,
subject_name VARCHAR(20)
);
-- Student_Subject Table (junction table with marks_scored)
CREATE TABLE Student_Subject (
rollno INT,
scode VARCHAR(10),
marks_scored INT,
PRIMARY KEY (rollno, scode),
FOREIGN KEY (rollno) REFERENCES Student(rollno),
FOREIGN KEY (scode) REFERENCES Subject(scode)
);
2. Insert Sample Records
sql
-- Insert into Student
INSERT INTO Student VALUES
(1, 'Amit', 'Mumbai', 'BScCS'),
(2, 'Sneha', 'Pune', 'BScIT'),
(3, 'Rahul', 'Mumbai', 'BCA');
-- Insert into Subject
INSERT INTO Subject VALUES
('S101', 'DBMS'),
('S102', 'AI'),
('S103', 'OS');
-- Insert into Student_Subject
INSERT INTO Student_Subject VALUES
(1, 'S101', 85),
(1, 'S102', 90),
(2, 'S101', 70),
(3, 'S103', 60);
3. Triggers
a) Trigger BEFORE INSERT/UPDATE on marks_scored Raise exception if marks are negative
sql
-- Function to check marks before insert/update
CREATE OR REPLACE FUNCTION check_marks_before_insert_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.marks_scored < 0 THEN
RAISE EXCEPTION 'Marks cannot be negative: %', NEW.marks_scored;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE INSERT
CREATE TRIGGER trg_marks_before_insert
BEFORE INSERT ON Student_Subject
FOR EACH ROW
EXECUTE FUNCTION check_marks_before_insert_update();
-- Trigger BEFORE UPDATE
CREATE TRIGGER trg_marks_before_update
BEFORE UPDATE ON Student_Subject
FOR EACH ROW
EXECUTE FUNCTION check_marks_before_insert_update();
Test
INSERT INTO Student_Subject VALUES (2, 'S102', -10);
-- ERROR: Marks cannot be negative: -10
b) Trigger on INSERT in Student_Subject to prevent negative marks Display message and prevent insertion
sql
-- Function to prevent negative marks on insert
CREATE OR REPLACE FUNCTION prevent_negative_marks_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.marks_scored < 0 THEN
RAISE EXCEPTION 'Insertion not allowed: Marks are negative (%).', NEW.marks_scored;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE INSERT
CREATE TRIGGER trg_prevent_negative_marks
BEFORE INSERT ON Student_Subject
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_marks_insert();
Test
sql
INSERT INTO Student_Subject VALUES (3, 'S102', -5);
-- ERROR: Insertion not allowed: Marks are negative (-5).
✅ Note: This trigger complements the previous one; ensures marks validation during insert.
c) Trigger to prevent deleting students from 'Mumbai'
sql
-- Function to prevent deletion of Mumbai students
CREATE OR REPLACE FUNCTION prevent_mumbai_student_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.city = 'Mumbai' THEN
RAISE EXCEPTION 'Cannot delete student from Mumbai: %', OLD.name;
END IF;
RETURN OLD;
END;
$$;
-- Trigger BEFORE DELETE
CREATE TRIGGER trg_prevent_mumbai_delete
BEFORE DELETE ON Student
FOR EACH ROW
EXECUTE FUNCTION prevent_mumbai_student_delete();
Test
sql
DELETE FROM Student WHERE rollno = 1;
-- ERROR: Cannot delete student from Mumbai: Amit
DELETE FROM Student WHERE rollno = 2;
-- Works fine (Sneha is from Pune)
SET D
Customer – Account database
Consider the following database
Customer (cno integer, cname varchar(20), city varchar(20))
Account (a_no int, a_type varchar(10), opening_date date, balance money) Customer
and Account are related with one to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger which is executed whenever update is made to the account table. If the balance
becomes less than 1000, print an error message that balance cannot be less than 1000.
b. Write a trigger before deleting an account record from Account table. Raise a notice and display
the message “Account record is being deleted.”
c. Write a trigger before inserting an account record in Account table and raise exception if
balance is <500.
-- Customer Table
CREATE TABLE Customer (
cno INT PRIMARY KEY,
cname VARCHAR(20),
city VARCHAR(20)
);
-- Account Table
CREATE TABLE Account (
a_no INT PRIMARY KEY,
cno INT REFERENCES Customer(cno),
a_type VARCHAR(10),
opening_date DATE,
balance MONEY
);
2. Insert Sample Records
-- Insert into Customer
INSERT INTO Customer VALUES
(1, 'Amit', 'Mumbai'),
(2, 'Sneha', 'Pune'),
(3, 'Rahul', 'Delhi');
-- Insert into Account
INSERT INTO Account VALUES
(101, 1, 'Savings', '2022-01-10', 5000),
(102, 2, 'Current', '2022-05-15', 1500),
(103, 3, 'Savings', '2022-03-20', 2000);
3. Triggers
a) Trigger on UPDATE of Account balance Raise exception if balance < 1000
sql
-- Function for balance check on update
CREATE OR REPLACE FUNCTION check_balance_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.balance < 1000 THEN
RAISE EXCEPTION 'Balance cannot be less than 1000. Current balance: %', NEW.balance;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE UPDATE
CREATE TRIGGER trg_check_balance_update
BEFORE UPDATE ON Account
FOR EACH ROW
EXECUTE FUNCTION check_balance_update();
Test
sql
UPDATE Account SET balance = 900 WHERE a_no = 101;
-- ERROR: Balance cannot be less than 1000. Current balance: 900
b) Trigger BEFORE DELETE on Account Display notice “Account record is being deleted.”
sql
-- Function for delete notice
CREATE OR REPLACE FUNCTION account_delete_notice()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Account record is being deleted. Account No: %', OLD.a_no;
RETURN OLD;
END;
$$;
-- Trigger BEFORE DELETE
CREATE TRIGGER trg_account_delete_notice
BEFORE DELETE ON Account
FOR EACH ROW
EXECUTE FUNCTION account_delete_notice();
Test
sql
DELETE FROM Account WHERE a_no = 103;
-- NOTICE: Account record is being deleted. Account No: 103
c) Trigger BEFORE INSERT on Account Raise exception if balance < 500
sql
-- Function for insert validation
CREATE OR REPLACE FUNCTION check_balance_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.balance < 500 THEN
RAISE EXCEPTION 'Insertion not allowed. Balance cannot be less than 500. Entered: %',
NEW.balance;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE INSERT
CREATE TRIGGER trg_check_balance_insert
BEFORE INSERT ON Account
FOR EACH ROW
EXECUTE FUNCTION check_balance_insert();
Test
sql
-- Valid insertion
INSERT INTO Account VALUES (104, 2, 'Savings', '2023-01-01', 2000);
-- Invalid insertion
INSERT INTO Account VALUES (105, 1, 'Current', '2023-01-05', 400);
-- ERROR: Insertion not allowed. Balance cannot be less than 500. Entered: 400
SET E
Project-Employee Database
Consider the following Entities and their Relationships for Project-Employee database.
Project (pno integer, pname char (30), ptype char (20), duration integer)
Employee (eno integer, ename char (20), qualification char (15), joining_date date)
Relationship between Project and Employee is many to many with descriptive
attribute start_date date, no_of_hours_worked integer.
Constraints: Primary Key, pname should not be null.
Create trigger for the following:
a. Write a trigger before inserting into an employee table to check current date should be always
greater than joining date. Display appropriate message.
b. Write a trigger before inserting into a project table to check duration should be always greater
than zero. Display appropriate message.
c. Write a trigger before deleting an employee record from employee table. Raise a notice and
display the message “Employee record is being deleted”.
-- Project Table
CREATE TABLE Project (
pno INT PRIMARY KEY,
pname CHAR(30) NOT NULL,
ptype CHAR(20),
duration INT
);
-- Employee Table
CREATE TABLE Employee (
eno INT PRIMARY KEY,
ename CHAR(20),
qualification CHAR(15),
joining_date DATE
);
-- Junction Table for Many-to-Many Relationship
CREATE TABLE Project_Employee (
pno INT,
eno INT,
start_date DATE,
no_of_hours_worked INT,
PRIMARY KEY (pno, eno),
FOREIGN KEY (pno) REFERENCES Project(pno),
FOREIGN KEY (eno) REFERENCES Employee(eno)
);
2. Insert Sample Records
sql
-- Insert into Project
INSERT INTO Project VALUES
(1, 'ERP System', 'Software', 12),
(2, 'Website Dev', 'Web', 6);
-- Insert into Employee
INSERT INTO Employee VALUES
(101, 'Amit', 'MScCS', '2020-01-10'),
(102, 'Sneha', 'MCA', '2019-07-15');
-- Insert into Project_Employee
INSERT INTO Project_Employee VALUES
(1, 101, '2022-01-01', 120),
(2, 102, '2023-02-01', 80);
3. Triggers
a) Trigger before inserting into Employee Check: joining_date should be less than or equal to
current_date
sql
-- Function for Employee insertion validation
CREATE OR REPLACE FUNCTION check_joining_date()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.joining_date > CURRENT_DATE THEN
RAISE EXCEPTION 'Joining date % cannot be in the future', NEW.joining_date;
ELSE
RAISE NOTICE 'Employee joining date is valid: %', NEW.joining_date;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE INSERT on Employee
CREATE TRIGGER trg_check_joining_date
BEFORE INSERT ON Employee
FOR EACH ROW
EXECUTE FUNCTION check_joining_date();
Test
sql
INSERT INTO Employee VALUES (103, 'Rahul', 'BScCS', '2027-01-01');
-- ERROR: Joining date 2027-01-01 cannot be in the future
INSERT INTO Employee VALUES (104, 'Neha', 'BCA', '2025-01-15');
-- NOTICE: Employee joining date is valid: 2025-01-15
b) Trigger before inserting into Project Check: duration should be greater than zero
sql
-- Function for Project insertion validation
CREATE OR REPLACE FUNCTION check_project_duration()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.duration <= 0 THEN
RAISE EXCEPTION 'Project duration must be greater than 0. Entered: %', NEW.duration;
ELSE
RAISE NOTICE 'Project duration is valid: %', NEW.duration;
END IF;
RETURN NEW;
END;
$$;
-- Trigger BEFORE INSERT on Project
CREATE TRIGGER trg_check_project_duration
BEFORE INSERT ON Project
FOR EACH ROW
EXECUTE FUNCTION check_project_duration();
Test
sql
INSERT INTO Project VALUES (3, 'AI Research', 'Research', -5);
-- ERROR: Project duration must be greater than 0. Entered: -5
INSERT INTO Project VALUES (3, 'AI Research', 'Research', 18);
-- NOTICE: Project duration is valid: 18
c) Trigger before deleting an Employee record Display notice: “Employee record is being deleted”
sql
-- Function for Employee deletion notice
CREATE OR REPLACE FUNCTION employee_delete_notice()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Employee record is being deleted. Employee No: %', OLD.eno;
RETURN OLD;
END;
$$;
-- Trigger BEFORE DELETE on Employee
CREATE TRIGGER trg_employee_delete_notice
BEFORE DELETE ON Employee
FOR EACH ROW
EXECUTE FUNCTION employee_delete_notice();
Test
sql
DELETE FROM Employee WHERE eno = 102;
-- NOTICE: Employee record is being deleted. Employee No: 102