ms7BaMjh

1--------------
2--Task 5
3--------------
4DROP TABLE IF EXISTS Person;
5CREATE TABLE Person (
6	id INTEGER NOT NULL PRIMARY KEY,
7	name VARCHAR(50)
8);
9DROP TABLE IF EXISTS Course;
10CREATE TABLE Course(
11	code VARCHAR(6),
12	pId INTEGER NOT NULL,
13	FOREIGN KEY (pId) references Person(id)
14);
15
16DROP TRIGGER IF EXISTS tr_pendant_delete;
17delimiter |
18CREATE TRIGGER tr_pendant_delete AFTER DELETE ON Course
19	FOR EACH ROW BEGIN
20	DECLARE result INTEGER;
21	SELECT COUNT(*) INTO result FROM Course WHERE pId=OLD.pId;
22	IF((result) < 1) THEN
23	DELETE FROM Person WHERE id=OLD.pId;
24	END IF;
25	END
26|
27delimiter ;
28
29Run:
30mysql> INSERT INTO Person(id, name) VALUES(1, 'Helge');
31Query OK, 1 row affected (0.01 sec)
32
33mysql> 
34mysql> INSERT INTO Course(code, pId) VALUES('TOD122', 1);
35Query OK, 1 row affected (0.00 sec)
36
37mysql> INSERT INTO Course(code, pId) VALUES('TOD166', 1);
38Query OK, 1 row affected (0.00 sec)
39
40mysql> select * from Person;
41+----+-------+
42| id | name  |
43+----+-------+
44|  1 | Helge |
45+----+-------+
461 row in set (0.00 sec)
47
48mysql> delete from Course WHERE pId=1;
49Query OK, 2 rows affected (0.01 sec)
50
51mysql> select * from Person;
52Empty set (0.00 sec)