--
创建用于监视对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
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