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