实验7-存储过程和触发器的使用

存储过程和触发器的使用
//2013.10.30
//实验7
USE YGGL
GO
CREATE PROCEDURE TEST@NUMBER1int OUTPUT
AS
BEGIN
DECLARE@NUMBER2int
SET@NUMBER2=(SELECT COUNT(*)FROM Employees)
SET@NUMBER1=@NUMBER2
END
DECLARE@num int
EXEC TEST@num OUTPUT
SELECT@num
CREATE PROCEDURE COMPA@ID1char(6),@ID2char(6),@BJ int OUTPUT
AS
BEGIN
DECLARE@SR1float,@SR2float
SELECT@SR1=InCome-OutCome FROM Salary WHERE EmployeeID=@ID1
SELECT@SR2=InCome-OutCome FROM Salary WHERE EmployeeID=@ID2
IF@ID1>@ID2
SET@BJ=0
ELSE
SET@BJ=1
END
DECLARE@BJ int
EXEC COMPA'000001','108991',@BJ OUTPUT
SELECT@BJ
USE YGGL
GO
CREATE PROCEDURE EmployeeAdd
(
@employeeid char(6),@name char(10),@education char(4),@birthday datetime,
@woekyear tinyint,@sex bit,@address char(40),@phonenumber char(12), @departmentID char(3)
)AS
BEGIN
INSERT INTO Employees
VALUES(@employeeid,@name,@education,@birthday,@woekyear,
@sex,@address,@phonenumber,@departmentID)
END
RETURN
GO
EXEC EmployeeAdd'990230','刘朝','本科','840909',2,1,'武汉小洪山号
','85465213','3'
USE YGGL
GO
CREATE PROCEDURE em_cursor@em_cursor cursor VARYING OUTPUT AS
BEGIN
SET@em_cursor=CURSOR FORWARD_ONLY STATIC
FOR
SELECT*FROM Employees
OPEN@em_cursor
END
GO
DECLARE@MyCursor cursor
EXEC em_cursor@em_cursor=@MyCursor OUTPUT/*执行存储过程*/ FETCH NEXT FROM@MyCursor
WHILE (@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM@MyCursor
END
CLOSE@MyCursor
DEALLOCATE@MyCursor
GO
CREATE PROCEDURE TOP_THREE@EM_ID char(6),@OK bit OUTPUT
AS
BEGIN
DECLARE@X_EM_ID char(6)
DECLARE@ACT_IN int,@SEQ int
DECLARE SALARY_DIS cursor FOR/*声明游标*/
SELECT EmployeeID,InCome-OutCome
FROM Salary
ORDER BY InCome-OutCome DESC
SET@SEQ=0
SET@OK=0
OPEN SALARY_DIS
FETCH SALARY_DIS INTO@X_EM_ID,@ACT_IN/*读取第一行数据*/ WHILE@SEQ<3 AND@OK=0 /*比较前三行数据*/
BEGIN
SET@SEQ=@SEQ+1
IF@X_EM_ID=@EM_ID
SET@OK=1
FETCH SALARY_DIS INTO@X_EM_ID,@ACT_IN
END
CLOSE SALARY_DIS
DEALLOCATE SALARY_DIS
END
DECLARE@OK BIT
EXEC TOP_THREE'108991',@OK OUTPUT
SELECT@OK
USE YGGL
GO
CREATE TRIGGER EmployeesIns ON dbo.Employees
FOR INSERT,UPDATE
AS
BEGIN
IF((SELECT DepartmentID from inserted)NOT IN
(SELECT DepartmentID FROM DepartmentS))
ROLLBACK/*对当前事务回滚，即恢复到插入前的状态*/ END
USE YGGL
GO
CREATE TRIGGER DepartmentUpdate ON dbo.Departments
FOR UPDATE
AS
BEGIN
UPDATE Employees
SET DepartmentID=(SELECT DepartmentID FROM insered)
WHERE DepartmentID=(SELECT DepartmentID FROM deleted) END
GO
CREATE TRIGGER DepartmentsDelate ON dbo.Departments
FOR DELETE
AS
BEGIN
DELETE FROM Employees
WHERE DepartmentID=(SELECT DepartmentID FROM deleted) END
GO
CREATE TRIGGER EM_EXISTS ON Salary
INSTEAD OF INSERT
AS
BEGIN
DECLARE@EmployeeID char(6)
SELECT@EmployeeID=EmployeeID
FROM inserted
IF(@EmployeeID IN(SELECT EmployeeID FROM Employees))
INSERT INTO Salary SELECT*FROM inserted
ELSE
PRINT'员工号不存在'
END
INSERT INTO Salary VALUES('111111',2500.3,123.2)
USE YGGL
GO
CREATE TRIGGER table_delete
ON DATABASE
AFTER DROP_TABLE
AS
PRINT'不能删除该表'
ROLLBACK TRANSACTION。