--创建分区表
CREATE TABLE T1(id int identity(1,1),dates datetime CHECK (datepart(mm,dates)=8))
CREATE TABLE T2(id int identity(1,1),dates datetime CHECK (datepart(mm,dates)=9))
GO--创建分区视图
CREATE VIEW V1 AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
GO--创建基于分区视图的INSTEAD OF INSERT触发器
CREATE TRIGGER TRI1 ON V1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO T1(DATES) SELECT DATES FROM INSERTED WHERE DATEPART(MM,DATES)=8
INSERT INTO T2(DATES) SELECT DATES FROM INSERTED WHERE DATEPART(MM,DATES)=9
END
GO--在视图上执行INSERT操作
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-15
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-30
--查看执行结果
/*
ID DATES
---- -----------------------
1 2005-08-24 11:11:59.357
2 2005-08-09 11:11:59.357
1 2005-09-08 11:11:59.357
*/--删除测试环境
DROP TRIGGER TRI1
CREATE TABLE T1(id int identity(1,1),dates datetime CHECK (datepart(mm,dates)=8))
CREATE TABLE T2(id int identity(1,1),dates datetime CHECK (datepart(mm,dates)=9))
GO--创建分区视图
CREATE VIEW V1 AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
GO--创建基于分区视图的INSTEAD OF INSERT触发器
CREATE TRIGGER TRI1 ON V1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO T1(DATES) SELECT DATES FROM INSERTED WHERE DATEPART(MM,DATES)=8
INSERT INTO T2(DATES) SELECT DATES FROM INSERTED WHERE DATEPART(MM,DATES)=9
END
GO--在视图上执行INSERT操作
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-15
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-30
--查看执行结果
/*
ID DATES
---- -----------------------
1 2005-08-24 11:11:59.357
2 2005-08-09 11:11:59.357
1 2005-09-08 11:11:59.357
*/--删除测试环境
DROP TRIGGER TRI1
解决方案 »
- sql server 2000收缩数据库
- 怎样把某个数据库中的一个表导入另一个数据库?
- 级联删除 利与bi,如何合理使用
- 请教在存储过程中执行SQL,如何把多个结果放入定义的变量中?
- sql server 2000的数据文件,还原到sql server 2005后遇到的问题
- 急急急急
- 用SQL语句该怎样写才能得到最大月份的记录呢?
- 100分请教Smo导出视图脚本问题
- oracle关于 procedure的。在线!
- vfp的一个表单问题100分!!!
- 不知道这个SQL应该怎么写呢...路过的SQL高手进来看看吧
- 怎样在一个查询中查询出一个表中相隔月份记录,并放在一条记录显示,我描述不太清晰,看表结构吧
-----------------------------------------------------------------------
--创建分区表
CREATE TABLE T1(id int,dates datetime CHECK (datepart(mm,dates)=8))
CREATE TABLE T2(id int,dates datetime CHECK (datepart(mm,dates)=9))
GO--创建分区视图
CREATE VIEW V1 AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
GO--创建基于分区视图的INSTEAD OF INSERT触发器
CREATE TRIGGER TRI1 ON V1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO T1(ID,DATES)
SELECT
(SELECT ISNULL(MAX(ID),0)+1 FROM V1),
DATES
FROM
INSERTED
WHERE
DATEPART(MM,DATES)=8
INSERT INTO T2(ID,DATES)
SELECT
(SELECT ISNULL(MAX(ID),0)+1 FROM V1),
DATES
FROM
INSERTED
WHERE
DATEPART(MM,DATES)=9
END
GO--在视图上执行INSERT操作
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-15
INSERT INTO V1(ID,DATES) SELECT 0,GETDATE()-30
--查看执行结果
SELECT * FROM V1 ORDER BY ID
/*
ID DATES
---- -----------------------
1 2005-09-08 11:21:23.967
2 2005-08-24 11:21:23.983
3 2005-08-09 11:21:23.983*/
--删除测试环境
DROP TRIGGER TRI1
DROP VIEW V1
DROP TABLE T1,T2