PL/SQL Stored Procedures and Triggers Assignment - University Database

This assignment solution demonstrates the implementation of PL/SQL stored procedures and triggers within a database context. The solution includes SQL scripts for creating and populating tables, as well as stored procedures for inserting new data into the 'person', 'post', and 'likes' tables. Furthermore, the solution addresses the creation of stored procedures with parameters, such as 'add_Marigold' and 'add_post', allowing for flexible data insertion. The solution also covers the creation of triggers, including 'post_BEFORE_INSERT', 'likes_BEFORE_INSERT', and 'post_AFTER_UPDATE', to enforce data validation and maintain data integrity. The assignment covers database design, SQL queries, and the use of stored procedures and triggers to manage and manipulate data within a relational database system. The solution addresses all the questions asked in the assignment brief.

Running head: PL/SQL STORED PROCEDURES AND TRIGGERS
PL/SQL Stored Procedures and Triggers
Name of the Student
Name of the University
Author’s note:

1PL/SQL STORED PROCEDURES AND TRIGGERS
Table of Contents
Answer to Question One:.................................................................................................................2
Answer to Question Two:................................................................................................................2
Answer to Question Three:..............................................................................................................3
Answer to Question Four:................................................................................................................4
Answer to Question Five:................................................................................................................5
Answer to Question Six:..................................................................................................................6
Answer to Question Seven:.............................................................................................................7
Answer to Question Eight:...............................................................................................................9
Answer to Question Nine:.............................................................................................................11
Answer to Question Ten:...............................................................................................................12
Bibliography:.................................................................................................................................14

2PL/SQL STORED PROCEDURES AND TRIGGERS
Answer to Question One:
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `user_name`) VALUES
('1', 'June', 'Stevens', 'June1999'), ('2', 'Frank', 'Howle', 'Frank1945'), ('3', 'Marc', 'Castro',
'Marc1979'), ('4', 'Frank', 'Lunt', 'Frank1989'), ('5', 'Eric', 'Caldwell', 'Eric1995');
INSERT INTO `post` (`post_id`, `person_id`, `content`, `created_on`, `summary`)
VALUES ('1', '1', 'I have reached Canada', '2019-04-01', '...I have reached'), ('2', '1', 'Having fun
in Starbucks', '2019-04-05', '...Having fun'), ('3', '2', 'Having a great evening with friends', '2019-
04-04', '...Having a great'), ('4', '3', 'Having a great evening with friends', '2019-04-04', '...Having
a great evening with friends'), ('5', '5', 'Having a dinner at my aunt\'s house', '2019-04-08',
'...Having a dinner'), ('6', '4', 'First time driving a car', '2019-04-03', '...First time driving'), ('7', '5',
'Just Married! So happy', '2019-04-13', '...Just married'), ('8', '3', 'So sad! my dog died today',
'2019-04-13', '...So sad!');
INSERT INTO `likes` (`likes_id`, `person_id`, `post_id`, `liked_on`) VALUES ('1', '5',
'1', '2019-04-16'), ('2', '3', '1', '2019-04-15'), ('3', '2', '3', '2019-04-13'), ('4', '4', '8', '2019-04-16');
Answer to Question Two:
USE `sns`;
DROP procedure IF EXISTS `add_Chris_Jones`;
DELIMITER $$
USE `sns`$$
CREATE PROCEDURE `add_Chris_Jones` ()

3PL/SQL STORED PROCEDURES AND TRIGGERS
BEGIN
insert into person values (6, 'Chirs', 'Jones', 'Chirs1993');
select * from person;
END$$
DELIMITER ;
Answer to Question Three:
call `add_Chris_Jones`;
The issues is occurring due to same primary key data. The primary key allows one value
for one row only. However, by calling the call `add_Chris_Jones` stored procedure for second
time, the SQL tries to insert the primary key ‘6’for two rows.

4PL/SQL STORED PROCEDURES AND TRIGGERS
Answer to Question Four:
USE `sns`;
DROP procedure IF EXISTS `add_Marigold`;
DELIMITER $$
USE `sns`$$
CREATE PROCEDURE `add_Marigold` (IN person_id Decimal(12), IN first_name
varchar(32), IN last_name varchar(32), IN user_name varchar(20))
BEGIN
insert into person values (person_id, first_name, last_name, user_name);
select * from person;
END$$
DELIMITER ;
Calling Procedure: set @person_id = 7, @first_name = 'John', @last_name = 'Smith',
@user_name = 'John1996';
CALL `sns`.`add_Marigold`(@person_id, @first_name, @last_name, @user_name);

5PL/SQL STORED PROCEDURES AND TRIGGERS
Answer to Question Five:
USE `sns`;
DROP procedure IF EXISTS `add_post`;
DELIMITER $$
USE `sns`$$
CREATE PROCEDURE `add_post` (IN post_id decimal(12), IN person_id decimal(12),
IN content varchar(255), IN created_on Date, IN summary varchar(15))
BEGIN
insert into post values (post_id, person_id, content, created_on, summary);
Select * from Post;
END$$

6PL/SQL STORED PROCEDURES AND TRIGGERS
DELIMITER ;
Calling Procedure: set @post_id = 9, @person_id = 4, @content = 'Having a cool
summar at miami beach', @created_on = '2019-4-04', @summary = '...Having a cool';
CALL `sns`.`add_post`(@post_id, @person_id, @content, @created_on, @summary);
Answer to Question Six:
USE `sns`;
DROP procedure IF EXISTS `add_likeposts`;
DELIMITER $$
USE `sns`$$
CREATE PROCEDURE `add_likeposts` (IN likes_id decimal(12), IN person_id
decimal(12), IN post_id decimal(12), IN liked_on Date)

7PL/SQL STORED PROCEDURES AND TRIGGERS
BEGIN
insert into likes values (likes_id, person_id, post_id, liked_on);
select * from likes;
END$$
DELIMITER ;
Calling Procedure: set @likes_id = 5, @person_id = 3, @post_id = 9, @liked_on =
'2019-6-04';
CALL `sns`.`add_likeposts`(@likes_id,@person_id, @post_id, @liked_on);
Answer to Question Seven:
USE `sns`;
DROP procedure IF EXISTS `delete_person`;

8PL/SQL STORED PROCEDURES AND TRIGGERS
DELIMITER $$
USE `sns`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_person`(IN user_id
varchar(20))
BEGIN
delete from person where user_name = user_id;
select * from person;
select * from post;
select * from likes;
END$$
DELIMITER ;
Calling Procedure: set @user_id = 'Frank1945';
CALL `sns`.`delete_person`(@user_id);

9PL/SQL STORED PROCEDURES AND TRIGGERS
Answer to Question Eight:
DROP TRIGGER IF EXISTS `sns`.`post_BEFORE_INSERT`;
DELIMITER $$
USE `sns`$$

10PL/SQL STORED PROCEDURES AND TRIGGERS
CREATE DEFINER = CURRENT_USER TRIGGER `sns`.`post_BEFORE_INSERT`
BEFORE INSERT ON `post` FOR EACH ROW
BEGIN
Declare short varchar(20);
select SUBSTRING(NEW.content, 1, 12) INTO short from dual;
if short != NEW.summary THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The summary is not formatted
properly';
END IF;
END$$
DELIMITER ;

11PL/SQL STORED PROCEDURES AND TRIGGERS
Answer to Question Nine:
DROP TRIGGER IF EXISTS `sns`.`likes_BEFORE_INSERT`;
DELIMITER $$
USE `sns`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `sns`.`likes_BEFORE_INSERT`
BEFORE INSERT ON `likes` FOR EACH ROW
BEGIN
DECLARE dt DATE;
select created_on INTO dt from post where post_id = NEW.post_id;