实验六 数据库编程
一、实验目的
1.掌握触发器的概念,了解触发器的类型
2.掌握存储过程的创建与执行方法
二、实验内容
创建触发器trigger_delete,实现以下功能:当订单表的数据被删除时,显示提示信息“订单表记录被修改了”。
CREATE TRIGGER TRIGGER_DELETE
ON Sell_Order
AFTER
DELETE
AS
PRINT'订单表记录被修改了'
对Sell_Order表创建名为reminder的触发器,当用户向Sell_Order表中插入或修改记录时,自动显示Sell_Order表中的记录。
CREATE TRIGGER TRIGGER_REMINDER
ON Sell_Order
AFTER
UPDATE
AS
SELECT *
FROM Sell_Order
对Employee表中创建名为emp_updtri的触发器,实现如下功能:当修改姓名时,自动检查订单表,确定是否有该员工的订单,如果存在该员工,则撤销操作。
CREATE TRIGGER EMP_UPDTRI
ON Employee
FOR UPDATE
AS
IF UPDATE(EmployeeName)
DECLARE @ID INT
BEGIN
SELECT @ID=EmployeeID
FROM deleted
IF(SELECT COUNT(@ID)
FROM deleted,Sell_Order
WHERE @ID=Sell_Order.EmployeeID)!=0
BEGIN
PRINT 'ERROR' ROLLBACK
END
END
创建一个INSERT触发器,当在Employee表中插入一条新员工记录时,如果是“人事部”的员工,则撤销该插入操作,并返回出错消息。
CREATE TRIGGER EmployeeName_INSERT
ON Employee
INSTEAD OF INSERT
AS
DECLARE @DEPARTMENTNAME VARCHAR(50)
SELECT @DEPARTMENTNAME=EmployeeName
FROM Department JOIN inserted
ON Department.DepartmentID=inserted.DepartmentID
IF(@DEPARTMENTNAME='人事部')
BEGIN
PRINT '此为人事部门的员工,撤销插入操作'
ROLLBACK
END
创建一个product_order_delete的触发器,其功能是:当删除商品表中的商品记录时,同时删除订单表中相应的订单,并显示提示信息“有关商品已被删除”。
CREATE TRIGGER PRODUCT_ORDER_DELETE
ON Product
FOR DELETE
AS
DECLARE @PID INT
SELECT @PID=ProductID
FROM deleted
DELETE
FROM Purchase_order
WHERE @PID=Purchase_order.ProductID
PRINT '有关商品已被删除'
创建一个名为employee_deleted的触发器,其功能是:当对Employee表进行删除操作时,首先检查订单表,如果删除的员工没有接收订单,可以删除该员工的消息,否则撤销删除,显示“无法修改”的信息。
CREATE TRIGGER EMPLOYEE_DELETE
ON Employee
FOR DELETE
AS
DECLARE @EID INT
SELECT @EID=EmployeeID
FROM deleted
IF(
SELECT COUNT(@EID)
FROM deleted,Sell_Order
WHERE @EID=Sell_Order.EmployeeID)!=0
BEGIN
PRINT '无法修改的信息'
ROLLBACK
END