数据库实验六

1.创建表SAV1, 记录每个公司所有员工的平均工资，其主要字段包括company_name, avg_salary, 其中company_name为主码。

CREATE TABLE SAV1 (
	company_name VARCHAR (30) NOT NULL,
	avg_salary DECIMAL (8),
	PRIMARY KEY (company_name)
);

INSERT into sav1  (
	SELECT
		company_name,
		AVG(salary) as avg_salary
	FROM
		works
	GROUP BY
		company_name
)

2.在表works上定义一个update触发器trig_update。当修改某个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

CREATE TRIGGER trig_update AFTER UPDATE ON works FOR EACH ROW
BEGIN
	UPDATE SAV1
SET avg_salary = (
	SELECT
		avg(salary)
	FROM
		works
	WHERE
		works.company_name = old.company_name
)
WHERE
	sav1.company_name = old.company_name;
END;

3.修改‘Curry’的工资为19000，观察表SAV1的变化。

SELECT	* FROM	sav1;

UPDATE works
SET salary = 19000
WHERE
	employee_ID = (
		SELECT
			employee_ID
		FROM
			employee
		WHERE
			employee_name = 'Curry'
	);

SELECT	* FROM	sav1;

4.在表works上定义一个insert触发器trig_insert。当插入一个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

CREATE TRIGGER trig_insert AFTER INSERT ON works FOR EACH ROW
BEGIN
	UPDATE sav1
SET avg_salary = (
	SELECT
		AVG(salary)
	FROM
		works
	WHERE
		works.company_name = new.company_name
)
WHERE
	sav1.company_name = new.company_name;
END;

5.插入一个新的员工：ID为319024，姓名为Bob，它居住的城市city是‘Foshan’，街道street是‘Second Road’，工作的公司名为‘Baidu’，工资是18500。观察表SAV1的变化。

SELECT * from sav1;

INSERT INTO employee VALUES(319024,'Bob','Second Road','Foshan');

INSERT INTO works VALUES(319024,'Baidu',18500);

SELECT * FROM sav1;

6.在表works上定义一个delete触发器trig_delete。当删除一个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

CREATE TRIGGER trig_delete AFTER DELETE ON works FOR EACH ROW
BEGIN
	UPDATE sav1
SET avg_salary = (
	SELECT
		AVG(salary)
	FROM
		works
	WHERE
		works.company_name = old.company_name
)
WHERE
	sav1.company_name = old.company_name;
END;

7.在表works中删除姓名为‘MrDeng’的员工的信息。观察表SAV1的变化。

SELECT * FROM sav1;
DELETE
FROM
	works
WHERE
	works.employee_ID = (
		SELECT
			employee_ID
		FROM
			employee
		WHERE
			employee.employee_name = 'MrDeng'
	);
SELECT * FROM sav1;

8.创建一个update_salary的触发器，每次向works表更新数据之后都会向名称为myevent的数据表中插入一条记录，其中myevent表包含三个属性：employee_ID，employee_name和 salary。

CREATE TABLE myevent (
	employee_ID INT NOT NULL,
	employee_name VARCHAR (30),
	salary DECIMAL (8),
	PRIMARY KEY (employee_ID)
);

CREATE TRIGGER update_salary AFTER INSERT ON works FOR EACH ROW
BEGIN
	INSERT INTO myevent
VALUES
	(
		new.employee_ID,
		(
			SELECT
				employee_name
			FROM
				employee
			WHERE
				employee.employee_ID = new.employee_ID
		),
		new.salary
	);
END;

9.插入一个新的员工ID为319025，姓名为‘Cara’，其工作的公司为‘Alibaba’，工资为15000。观察表myevent的变化。

SELECT * FROM myevent;

INSERT INTO employee VALUES(319025,'Cara',null,null);
INSERT INTO works VALUES(319025,'Alibaba',15000);

SELECT * FROM myevent;

10.删除触发器trig_update。

DROP TRIGGER trig_update

11.使用下面两种方式查看触发器的信息：（1）使用SHOW TRIGGERS语句查看触发器的信息；（2）在TRIGGERS表中查看触发器的信息。

SHOW TRIGGERS

思考题：
（1）对于相同的表和相同的事件，能否创建多个触发器？请举例说明。

可以对相同的表和相同的事件创建多个不同名的触发器

（2）是否要及时删除不要需要的触发器？

需要及时删除不需要的触发器，防止在对一个表进行修改是对另一个表产生影响。