创建用于监视对student表进行插入和更新操作的触发器

-- 创建用于监视对student表进行插入和更新操作的触发器

IF   EXISTS  ( SELECT  name  FROM  master.dbo.sysdatabases  WHERE  name  =  N ' StudentTest ' )
     DROP   DATABASE   [ StudentTest ]
GO

CREATE   DATABASE   [ StudentTest ]    ON  (NAME  =  N ' carTest ' , FILENAME  =  N ' D:\SqlData\StudentTest.mdf '  , SIZE  =   2 , FILEGROWTH  =   10 % )  LOG   ON  (NAME  =  N ' carTest_log ' , FILENAME  =  N ' D:\SqlData\StudentTest_log.LDF '  , FILEGROWTH  =   10 % )
GO


use  StudentTest
GO
if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[student] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ student ]
GO

create   table  student 
(
  id  uniqueidentifier   default   newid (),
  name  nvarchar ( 50 ),
  address  nvarchar ( 50 ),
  registeDate  datetime   default   getdate ()
   primary   key (id)
)

insert   into  student (name,address ) values ( ' frj ' , ' hunan ' )
insert   into  student (name,address ) values ( ' hlk ' , ' shiquan ' )
insert   into  student (name,address ) values ( ' hlb ' , ' shiquan ' )
-- select * from student

select   top   2  name ,address  from  student
go

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[InsertRecordTable] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ InsertRecordTable ]
GO


create   table  InsertRecordTable
(
  TableName           nvarchar ( 50 ),      -- 插入数据的表名
  NumOfRecord         uniqueidentifier ,  -- 插入数据的纪录编号
  InsertDate          DateTime   default   getdate (),  -- 插入数据的日期
  NumOfCheckpoint     nvarchar ( 50 ),      -- 检测站编号
  ReserveFild1  nvarchar ( 50 ),            -- 保留字段1
  ReserveFild2  nvarchar ( 50 ),            -- 保留字段2
  ReserveFild3  nvarchar ( 50 )             -- 保留字段3 
)

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[UpdateRecordTable] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ UpdateRecordTable ]
GO
create   table  UpdateRecordTable
(
  TableName           nvarchar ( 50 ),      -- 插入数据的表名
  NumOfRecord         uniqueidentifier ,  -- 插入数据的纪录编号
  UpdateDate          DateTime   default   getdate (),  -- 插入数据的日期
  NumOfCheckpoint     nvarchar ( 50 ),      -- 检测站编号
  ReserveFild1  nvarchar ( 50 ),            -- 保留字段1
  ReserveFild2  nvarchar ( 50 ),            -- 保留字段2
  ReserveFild3  nvarchar ( 50 )             -- 保留字段3 
)

GO
-- 创建监视插入数据的触发器
if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[student_insert] ' )  and   OBJECTPROPERTY (id, N ' IsTrigger ' )  =   1 )
drop   trigger   [ dbo ] . [ student_insert ]
go
CREATE   TRIGGER  student_insert  ON  student
FOR   INSERT
AS
DECLARE   @id   uniqueidentifier
DECLARE  c1  CURSOR   FOR
    SELECT  inserted.id
    FROM    student, inserted
    WHERE  student.id  =  inserted.id
OPEN  c1
FETCH   NEXT   FROM  c1  INTO   @id
WHILE   @@fetch_status   =   0
BEGIN
    INSERT   INTO  InsertRecordTable (TableName,NumOfRecord)  VALUES ( ' STUDENT ' , @id )
    FETCH   NEXT   FROM  c1  INTO   @id
END
CLOSE  c1
DEALLOCATE  c1
GO

--  Insert some test data rows.
delete   from  student  where  name = ' frj888 '   or  name = ' frj999 '   OR  NAME = ' HAO888 '   OR  NAME = ' HAO999 '
INSERT  student(name, address)  VALUES  ( ' frj888 ' ,  ' hunan ' )
INSERT  student(name, address)  VALUES  ( ' frj999 ' ,  ' shiquan ' )

GO
SELECT   *   FROM  student
GO

SELECT   *   FROM  insertrecordtable
GO


-- 创建用于监视对student表进行更新数据的触发器
if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[student_update] ' )  and   OBJECTPROPERTY (id, N ' IsTrigger ' )  =   1 )
drop   trigger   [ dbo ] . [ student_update ]

GO
CREATE   TRIGGER  student_update  ON  student  FOR   UPDATE
AS
IF   UPDATE  (name)
BEGIN
DECLARE   @id   uniqueidentifier
DECLARE  c1  CURSOR   FOR
    SELECT  deleted.id
    FROM    student, deleted
    WHERE  student.id  =  deleted.id
OPEN  c1
FETCH   NEXT   FROM  c1  INTO   @id
WHILE   @@fetch_status   =   0
BEGIN
    INSERT   INTO  UpdateRecordTable (TableName,NumOfRecord)  VALUES ( ' STUDENT ' , @id )
    FETCH   NEXT   FROM  c1  INTO   @id
END
CLOSE  c1
DEALLOCATE  c1
END
GO

UPDATE  STUDENT  SET  NAME = ' HAO999 '
WHERE  NAME  =   ' FRJ999 '
UPDATE  STUDENT  SET  ADDRESS = ' HAO888 '
WHERE  NAME  =   ' FRJ888 '
GO
SELECT   *   FROM  UpdateRecordTable
select   *   from  student
GO