SQL脚本整理系列 三

触发器

SQL 2008 怎么实现删除学生表里面的一条记录，成绩表里面关于这个学生的记录也同时删掉，谢求具体代码

--创建表
IF OBJECT_ID('tstudent') > 0
    DROP TABLE tstudent
GO 
CREATE TABLE tstudent
    (
      fstudentno VARCHAR(10) PRIMARY KEY ,
      fname NVARCHAR(10),
    )

IF OBJECT_ID('tScore') > 0
    DROP TABLE tScore
CREATE TABLE tScore
    (
      fstudentno VARCHAR(10) PRIMARY KEY ,
      fscroe INT
    )
GO


--插入测试数据
INSERT  dbo.tstudent
        ( fstudentno, fname )
VALUES  ( '001', -- fstudentno - varchar(10)
          N'小张'  -- fname - nvarchar(10)
          )
          
INSERT  dbo.tScore
        ( fstudentno, fscroe )
VALUES  ( '001', -- fstudentno - varchar(10)
          90  -- fscroe - int
          )
          
 --查看插入效果
SELECT  *
FROM    dbo.tScore
SELECT  *
FROM    dbo.tstudent

--创建触发器
IF ( OBJECT_ID('tgr_DeleteByStudentNo', 'tr') IS NOT NULL )
    DROP TRIGGER tgr_DeleteByStudentNo
    GO
--创建一个删除的触发器
CREATE  TRIGGER tgr_DeleteByStudentNo ON tstudent
    AFTER DELETE
AS
    DECLARE @strStudentNo VARCHAR(10)
    SELECT  @strStudentNo = fstudentno
    FROM    deleted
    
    DELETE  FROM tScore
    WHERE   fstudentno = @strStudentNo



    DELETE  FROM tstudent
    WHERE   fstudentno = '001'

 --查看删除效果
    SELECT  *
    FROM    dbo.tScore
    SELECT  *
    FROM    dbo.tstudent

  
  

 计算列

sql中 能否新建一个字段，字段的计算列中写移动平均的计算公式，如果不能应如何计算移动平均

解决方法：

新增2列：一列FF的默认值为一个函数，函数计算上面4行和值

一列为计算列 计算当前行 （Score +FF）/5

CREATE TABLE [dbo].[tScore](
    [fstudentno] [VARCHAR](10) NOT NULL,
    [fscroe] [INT] NULL,
    [fDate] [DATETIME] NOT NULL,
    [ff] [DECIMAL](18, 3) NULL,
    [AvgScore1]  AS (([fscroe]+[ff])/(5)) PERSISTED,
 CONSTRAINT [PK__tScore__9DB95AB24AB81AF0] PRIMARY KEY CLUSTERED 
(
    [fstudentno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tScore] ADD  CONSTRAINT [DF_tScore_fDate]  DEFAULT (GETDATE()) FOR [fDate]
GO

ALTER TABLE [dbo].[tScore] ADD  CONSTRAINT [DF_tScore_ff]  DEFAULT ([dbo].[fn_GetAvg]()) FOR [ff]
GO

CREATE FUNCTION fn_GetAvg()
RETURNS decimal(18,3)
as
BEGIN 
 -- 自己统计上面4行数据的和值
    RETURN 1.2
END