The following is the script for the SQL: set sql_safe_updates=0; drop table if exists message; drop table if exists person; create table person ( pid integer primary key, pname varchar(20) not null, msgs_sent integer default 0, msgs_rcvd integer default 0 ); create table message ( msg varchar(200) not null, sendtime datetime, senderid integer, receiverid integer, constraint message_pk primary key (sendtime,senderid,receiverid), constraint message_fk1 foreign key (senderid) references person(pid), constraint message_fk2 foreign key (receiverid) references person(pid) ); DROP TRIGGER IF EXISTS MESSAGE_SEND; set sql_safe_updates=0; DELIMITER $$ CREATE TRIGGER MESSAGE_SEND BEFORE INSERT ON message FOR EACH ROW BEGIN update person set msgs_sent = msgs_sent+1 where pid=new.senderid; update person set msgs_rcvd = msgs_rcvd+1 where pid=new.receiverid; END $$ DELIMITER ; In SQL script create a trigger that fires on deletes to 'message', to keep the sent/received counts correct even when messages are deleted. Test it by a. Creating some message from BATMAN0 to BATMAN1 b. Doing SELECT * FROM person WHERE pid=1000; c. Deleting the message you just created. d. Doing SELECT * FROM person WHERE pid=1000; which should show the mesgs_sent decreasing by 1.
The following is the script for the SQL:
set sql_safe_updates=0;
drop table if exists message;
drop table if exists person;
create table person ( pid integer primary key, pname varchar(20) not null, msgs_sent integer default 0, msgs_rcvd integer default 0 );
create table message ( msg varchar(200) not null, sendtime datetime, senderid integer, receiverid integer, constraint message_pk primary key (sendtime,senderid,receiverid),
constraint message_fk1 foreign key (senderid) references person(pid),
constraint message_fk2 foreign key (receiverid) references person(pid) );
DROP TRIGGER IF EXISTS MESSAGE_SEND;
set sql_safe_updates=0;
DELIMITER $$
CREATE TRIGGER MESSAGE_SEND BEFORE INSERT ON message
FOR EACH ROW
BEGIN
update person set msgs_sent = msgs_sent+1 where pid=new.senderid;
update person set msgs_rcvd = msgs_rcvd+1 where pid=new.receiverid;
END $$
DELIMITER ;
In SQL script create a trigger that fires on deletes to 'message', to keep the sent/received counts correct even when messages are deleted. Test it by
a. Creating some message from BATMAN0 to BATMAN1
b. Doing SELECT * FROM person WHERE pid=1000;
c. Deleting the message you just created.
d. Doing SELECT * FROM person WHERE pid=1000; which should show the mesgs_sent decreasing by 1.
Step by step
Solved in 2 steps with 1 images
