存储过程和触发器

--1.返回号学生的情况。该存储过程不使用哪个任何参数

CREATE PROCEDURE student_info

   AS

      SELECT *

      FROM CJB

      WHERE 学号='081101'

--2。从pxscj数据库三个表中查询某人指定课程的成绩和学分

--该存储过程接受与传递参数精确匹配值

CREATE PROCEDURE student_info1 @name char(8),@cname char(16)

   AS

      SELECT a.学号,姓名,课程名,成绩,t.学分

      FROM XSB a INNER JOIN CJB b

      ON a.学号=b.学号 INNER JOIN KCB t

      ON b.课程号=t.课程号

      WHERE a.姓名=@name and t.课程名=@cname

   EXECUTE student_info1 '王林','计算机基础'

--3.创建一个存储过程为do——insert，作用是向表xsb中插入一行数据，创建另一个存储过程do——action，

--在其中调用另一个存储过程，并根据条件处理该行数据，处理后输出相应的信息

CREATE PROCEDURE dbo.do_insert

   AS

   INSERT INTO XSB VALUES('091201','陶伟',1,'1990-03-05','软件工程',50,NULL)

  

   CREATE PROCEDURE do_action @x bit,@STR char(8) OUTPUT

   AS

      BEGIN

        EXEC do_insert

        IF @x=0

        BEGIN

           UPDATE XSB SET 姓名='刘英',性别=0 WHERE 学号='091201'

           SET @STR='修改成功'

           END

           ELSE

              IF @x=1

              BEGIN

                 DELETE FROM XSB WHERE 学号='091201'

                 SET @STR='删除成功'

                 END

      END

  

DECLARE @str char(8)

EXEC dbo.do_action 0,@str OUTPUT

SELECT @str

USE YGGL

GO

--4.从三个表链接中返回学生的学号,姓名,所选课程及该课程的成绩

--该存储过程在参数中使用了模式匹配,若没有提供参数,则使用预设的默认值

CREATE PROCEDURE st_info @name varchar(30)='李%'

   AS

   SELECT a.学号,a.姓名,c.课程名,b.成绩

   FROM XSB a INNER JOIN CJB b

   ON a.学号=b.学号 inner JOIN KCB c

   ON c.课程号=b.课程号

   WHERE 姓名 LIKE @name

--5.在pxscj数据库的表XSB上声明并打开一个游标

CREATE PROCEDURE st_cursor @st_cursor cursor VARYING OUTPUT

   AS

      SET @st_cursor =CURSOR FORWARD_ONLY STATIC FOR

        SELECT *

           FROM XSB

        OPEN @st_cursor

       

DECLARE @MyCursor cursor

EXEC st_cursor @st_cursor=@MyCursor OUTPUT

FETCH NEXT FROM @MyCursor

WHILE (@@FETCH_STATUS =0)

   BEGIN

      FETCH NEXT FROM @MyCursor

   END

      CLOSE @MyCorsor

      DEALLOCATE @MyCursor

     

--6.创建加密过程，使用sp_help系统存储过程获取关于加密过程的信息，

--然后尝试直接从syscomments表中获取关于该过程的信息

CREATE PROCEDURE encrypt_this WITH ENCRYPTION

   AS

      SELECT *

        FROM XSB

        EXEC sp_helptext encrypt_this

       

--存储过程的修改

--7.对例.2中创建的存储过程student_info1进行修改，将第一个参数改成学生的学号

ALTER PROCEDURE student_info1

   @number char(6),@cname char(16)

   AS

      SELECT 学号,课程名,成绩

        FROM CJB,KCB

        WHERE CJB.学号=@number AND KCB.课程名=@cname

       

--8.创建名为select_student 的存储过程，在默认情况下，该存储过程课查询所有学生信息

--然后授予权限。当该存储过程需更改为能检索计算机专业的学生信息时，用ALTER PROCEDURE重新定义

--（）创建select_students存储过程

CREATE PROCEDURE select_students

   AS

      SELECT *

        FROM XSB

        ORDER BY 学号

       

--(2)修改存储过程select_students

ALTER PROCEDURE select_students WITH ENCRYPTION

   AS

      SELECT *

        FROM XSB

        WHERE 专业='计算机'

        ORDER BY 学号

--存储过程的删除

--9.删除PXSCJ数据库中的student_info存储过程

IF EXISTS(SELECT name FROM sysobjects WHERE name='student_info')

   DROP PROCEDURE student_info

 

触发器

--10.创建一个表table1,其只中有一列a。在表上创建一个触发器，每次插入操作时，将变量@str的值设置为“TRIGGER IS WORKING”并显示

CREATE TABLE table1(a int)

GO

CREATE TRIGGER table1_insert

      ON table1 AFTER INSERT

   AS

   BEGIN

      DECLARE @str char(50)

      SET @str='TRIGGER IS WORKING'

      PRINT @str

   END

   --向table1中插入一行数据

INSERT INTO table1 VALUES(10)

 --11.创建触发器，当向CJB中插入一个学生的成绩时，将XSB中该学生的总学分加上添加的课程的学分

CREATE TRIGGER cjb_insert

   ON CJB AFTER INSERT

   BEGIN

      DECLARE @num char(6),@kc_num char(3)

      DECLARE @xf int

      SELECT @num=学号,@kc_num=课程号 from inserted

      SELECT @xf=学分 FROM KCB WHERE 课程号=@kc_num

      UPDATE XSB SET 总学分=总学分+@xf WHERE 学号=@num

      PRINT '修改成功'

   END

--12.创建触发器，定修改XSB中的学号时，同时也要将XSB中的学号修改成相应的学号

--（假设xsb和cjb无定义外键约束）

CREATE TRIGGER xsb_update

   ON XSB AFTER UPDATE

   BEGIN

      DECLARE @old_num char(6),@new_num char(6)

      SELECT @old_num=学号 FROM deleted

      SELECT @new_num=学号 FROM inserted

      UPDATE CJB SET 学号=@new_num WHERE 学号=@old_num

      PRINT '修改成功'

   END

   --接着修改表XSB中的一行数据，并查看触发器执行结果

   UPDATE XSB SET 学号='081120' WHERE 学号='081101'

   GO

   SELECT * FROM CJB WHERE 学号='081120'

--13.在删除XSB中的一条学生记录时将CJB中该学生的相应记录也删除。

CREATE TRIGGER xsb_delete

   ON XSB AFTER DELETE

   AS

   BEGIN

      DELETE FROM CJB

        WHERE 学号 IN(SELECT 学号 FROM deleted)

   END

--14.在KCB中创建UPDATE和DELETE触发器，当修改或删除KCB中的“课程号”字段时，

--同时修改或删除CJB中的该课程号

CREATE TRIGGER kcb_trig

   ON KCB AFTER UPDATE,DELETE

   AS

   BEGIN

      IF (UPDATE(课程号))

        UPDATE CJB SET 课程号=(SELECT 课程号 FROM inserted)

           WHERE 课程号=(SELECT 课程号 FROM deleted)

      else

        DELETE FROM CJB

           WHERE 课程号 IN(SELECT 课程号 FROM deleted)

      END

--15.创建表table2，值包含一列a，在表中创建INSTEAD OF INSERT 触发器

--当向表中插入记录时显示相应消息

CREATE TABLE table2(a int)

GO

CREATE TRIGGER table2_insert

   ON table2 INSTEAD OF INSERT

   AS

   PRINT 'INSTEAD OF TRIGGER IS WORKING'

   --向表中插入一行数据

   INSERT INTO table2 VALUES(10)

--16.在PXSCJ数据库中创建视图stu_view，包含学生，专业，课程号，成绩。

--该视图依赖于表XSB和CJB，是不可更新视图，可以在视图上创建INSTEAD OF触发器

--当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能

--首先，创建视图：

CREATE VIEW stu_view

AS

SELECT XSB.学号,专业,课程号,成绩

   FROM XSB,CJB

   WHERE XSB.学号=CJB.学号

   --创建INSTEAD OF触发器：

CREATE TRIGGER InsteadTrig

   ON stu_view

   INSTEAD OF INSERT

   AS

   BEGIN

      DECLARE @XH char(6),@XM char(8),

        @ZY char(12),@KCH char(3),@CJ int

        SELECT @XH=学号, @ZY=专业, @KCH=课程号, @CJ=成绩

           FROM inserted

        INSERT INTO XSB(学号,姓名,专业)

           VALUES(@XH,@XM,@ZY)

        INSERT INTO CJB VALUES(@XH,@KCH,@CJ)

   END

   --向视图插入一行数据

   INSERT INTO stu_view VALUES('091102','计算机','101',85)

   --查看数据是否插入

   SELECT * FROM stu_view WHERE 学号='091102'

   --查看与视图关联的XSB的情况

   SELECT * FROM XSB WHERE 学号='091102'

 

--17.创建PXSCJ数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作

CREATE TRIGGER safety

   ON DATABASE

   AFTER DROP_TABLE

   AS

      PRINT '不能删除该表'

      ROLLBACK TRANSACTION

      --尝试删除表table1：

      DROP TABLE table1

--18.创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作，然后回滚删除数据库的操作

CREATE TRIGGER safety_server

   ON ALL SERVER

   AFTER DROP_DATABASE

   AS

      PRINT '不能删除该表'

      ROLLBACK TRANSACTION

--19.修改PXSCJ数据库中在XSB上定义的触发器xsb_delete,将其修改为UPDATE触发器

ALTER TRIGGER xsb_delete ON XSB

   FOR UPDATE

   AS

   PRINT '执行的操作是修改'

--20.删除DML触发器xsb_delete

IF EXISTS (SELECT name FROM sysobjects WHERE name='xsb_delete')

   DROP TRIGGER xsb_delete

--21.删除DDL触发器safety

DROP TRIGGER safety ON DATABASE