数据库基础（五）：存储过程与触发器的创建、执行、修改、删除

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
  -- Add the parameters for the stored procedure here
  <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
  <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
    -- Insert statements for procedure here
  SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

CREATE PROCEDURE Pro_Qsinf
@SNO_IN CHAR(8)='S2',@SNAME_OUT CHAR(8) OUTPUT,
@SAGE_OUT INT OUTPUT, @DEPT_OUT CHAR(10) OUTPUT
AS SELECT @SNAME_OUT=SN,@SAGE_OUT=AGE,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN

USE jxsk
GO
DECLARE @SNO_IN CHAR(8),
@SNAME_OUT CHAR(8),
@SAGE_OUT INT,
@SDEPT_OUT CHAR(10)
EXEC Pro_Qsinf DEFAULT, @SNAME_OUT OUTPUT,@SAGE_OUT OUTPUT,@SDEPT_OUT OUTPUT
PRINT @SNAME_OUT
PRINT @SAGE_OUT
PRINT @SDEPT_OUT
SELECT @SNO_IN='S4'
EXEC PRO_QSINF @SNO_IN, @SNAME_OUT OUTPUT,@SAGE_OUT OUTPUT,@SDEPT_OUT OUTPUT
PRINT @SNAME_OUT
PRINT @SAGE_OUT
PRINT @SDEPT_OUT
GO

CREATE PROCEDURE Pro_Qscore
@SNAME_IN CHAR(8), @CNAME_IN CHAR(10),@SCORE_OUT TINYINT OUTPUT
AS SELECT @SCORE_OUT=SCORE FROM S,C,SC
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO
AND SN=@SNAME_IN AND CN=@CNAME_IN

USE jxsk
GO
DECLARE @SNAME_IN CHAR(8),
@CNAME_IN CHAR(8),
@SCORE_OUT TINYINT
SELECT @SNAME_IN='李思'
SELECT @CNAME_IN='程序设计'
EXEC PRO_QSCORE @SNAME_IN,@CNAME_IN,@SCORE_OUT OUTPUT PRINT RTRIM(@SNAME_IN)+'='+LTRIM (STR(@SCORE_OUT))
GO

USE [jxsk]
GO
/****** Object:  StoredProcedure [dbo].[Pro_Qsinf]    Script Date: 04/26/2019 15:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pro_Qsinf]
@SNO_IN CHAR(2)='S2',@SNAME_OUT CHAR(8) OUTPUT,
@SAGE_OUT TINYINT OUTPUT, @DEPT_OUT CHAR(10) OUTPUT
AS SELECT @SNAME_OUT=SN,@SAGE_OUT=AGE,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN

USE jxsk
GO
ALTER PROCEDURE PRO_QSINF
@SNO_IN CHAR(2)='S1',@SNAME_OUT CHAR(8) OUTPUT,
@SSEX_OUT CHAR(2) OUTPUT,@DEPT_OUT CHAR(10) OUT
AS 
SELECT @SNAME_OUT=SN,@SSEX_OUT=SEX,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN
GO

USE jxsk
GO
DROP PROCEDURE PRO_QSCORE
GO

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO

CREATE TRIGGER TRIGGER_S
ON S FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2)
SELECT @SNO_NEW=SNO FROM inserted
SELECT @SNO_OLD=SNO FROM deleted
UPDATE SC SET SNO=@SNO_NEW WHERE SNO=@SNO_OLD
END

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO

CREATE TRIGGER TRIGGER_SC
ON SC FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2),@SNO_CNT INT
SELECT @SNO_OLD=SNO FROM deleted
SELECT @SNO_CNT=COUNT(*) FROM S WHERE SNO=@SNO_OLD
IF @SNO_CNT<>0
ROLLBACK TRANSACTION
END

USE jxsk
GO
CREATE TRIGGER SCORE_SC_TRI
ON SC FOR INSERT,UPDATE
AS DECLARE @SCORE_READ TINYINT
SELECT @SCORE_READ=SCORE FROM inserted
IF @SCORE_READ >=0 AND @SCORE_READ<=100
BEGIN PRINT'操作完成！'
return
end
PRINT '成绩超出0-100之间，请重新输入。'
ROLLBACK TRANSACTION
GO

INSERT INTO SC VALUES('S1','C5',190)
GO
INSERT INTO SC VALUES('S1','C5',100)
GO
UPDATE SC SET SCORE=130 WHERE SNO='S2'AND CNO='C5'
GO
UPDATE SC SET SCORE =60 WHERE SNO='S2' AND CNO='C5'
GO

USE jxsk
GO
CREATE TRIGGER TRIGGER_DC
ON C FOR DELETE
AS DECLARE @CNO_DEL CHAR(2)
SELECT @CNO_DEL=CNO FROM deleted
DELETE FROM SC WHERE CNO=@CNO_DEL
GO

USE jxsk
GO
DELETE FROM C WHERE CNO='C1'
GO

USE [jxsk]
GO
/****** Object:  Trigger [dbo].[TRIGGER_S]    Script Date: 04/26/2019 16:25:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIGGER_S]
ON [dbo].[S] FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2)
SELECT @SNO_NEW=SNO FROM inserted
SELECT @SNO_OLD=SNO FROM deleted
UPDATE SC SET SNO=@SNO_NEW WHERE SNO=@SNO_OLD
END

ALTER TRIGGER TRIGGER_S
ON S FOR DELETE
AS DECLARE @SNO_DEL CHAR(2)
SELECT @SNO_DEL=SNO FROM deleted
WHERE SNO=@SNO_DEL

USE jxsk
GO
ALTER TRIGGER TRIGGER_DC
ON C FOR DELETE
AS DECLARE @CNO_DEL CHAR(2)
SELECT @CNO_DEL=CNO FROM deleted
DELETE FROM SC WHERE CNO=@CNO_DEL
DELETE FROM TC WHERE CNO=@CNO_DEL
GO

USE jxsk
GO
DROP TRIGGER TRIGGER_DC
GO