能帮我看看这段触发器插入新建表数据有什么问题吗？

混乱之序 2010-09-16 09:56:29



if exists(select name from sysobjects where name='tr_insert_RealTimeProcess' and type='tr')

drop trigger tr_insert_RealTimeProcess

go

create trigger tr_insert_RealTimeProcess

on T_RealTimeProcess

for insert

as

declare @sql varchar(100)

declare @TableName varchar(50)

declare @TableDate varchar(50)

declare @HistoryTable varchar(100)



declare @PersonID int

declare @MineID int

declare @CurrentMinePersonCount int

declare @ItemCreatedTime varchar(20)

declare @InOutMineMark smallint

declare @InMineTime varchar(20)

declare @OutMineTime varchar(20)

declare @CurrentAreaID int

declare @CurrentAreaPersonCount int

declare @InCurrentAreaTime varchar(20)

declare @CurrentSubstationID int

declare @CurrentSubstationPersonCount varchar(20)

declare @InCurrentSubstationTime varchar(20)

declare @SubstationIDAndTime varchar(8000)

declare @InsertTime varchar(20)

declare @UpdateTime varchar(20)



if @@rowcount>0

	set @TableName='T_ProcessHistory';

	set @TableDate='_'+replace(convert(varchar(10),getdate(),120),'-','_');

	set @HistoryTable=@TableName+@TableDate;

	exec CreateTable_ProcessHistory @TableName,@TableDate

	

	SELECT @PersonID=PersonID, @MineID=MineID, @CurrentMinePersonCount=CurrentMinePersonCount, @ItemCreatedTime=ItemCreatedTime, @InOutMineMark=InOutMineMark, 

      @InMineTime=InMineTime, @OutMineTime=OutMineTime, @CurrentAreaID=CurrentAreaID, @CurrentAreaPersonCount=CurrentAreaPersonCount, 

      @InCurrentAreaTime=InCurrentAreaTime, @CurrentSubstationID=CurrentSubstationID, @CurrentSubstationPersonCount=CurrentSubstationPersonCount, 

      @InCurrentSubstationTime=InCurrentSubstationTime, @SubstationIDAndTime=SubstationIDAndTime, @UpdateTime=UpdateTime, @InsertTime=InsertTime

	FROM inserted

begin

	 set @sql='insert '+@HistoryTable+' (PersonID, MineID, CurrentMinePersonCount, ItemCreatedTime, InOutMineMark, 

      InMineTime, OutMineTime, CurrentAreaID, CurrentAreaPersonCount, 

      InCurrentAreaTime, CurrentSubstationID, CurrentSubstationPersonCount, 

      InCurrentSubstationTime, SubstationIDAndTime, UpdateTime, InsertTime)values(

			@PersonID, @MineID, @CurrentMinePersonCount, @ItemCreatedTime, @InOutMineMark, 

      @InMineTime, @OutMineTime, @CurrentAreaID, @CurrentAreaPersonCount, 

      @InCurrentAreaTime, @CurrentSubstationID, @CurrentSubstationPersonCount, 

      @InCurrentSubstationTime, @SubstationIDAndTime, @UpdateTime, @InsertTime)'

exec 	(@sql)

end



--testT_RealTimeProcess

insert T_RealTimeProcess (PersonID, MineID, CurrentMinePersonCount, ItemCreatedTime, InOutMineMark, 

      InMineTime, OutMineTime, CurrentAreaID, CurrentAreaPersonCount, 

      InCurrentAreaTime, CurrentSubstationID, CurrentSubstationPersonCount, 

      InCurrentSubstationTime, SubstationIDAndTime, UpdateTime, InsertTime)values(

			1, 2, 21, getdate(), 1, 

      '1111', '2222', 3, 2, 

      '333', 4, 43, 

      '444', '5566', '123', '321')



select * from T_RealTimeProcess



select * from T_ProcessHistory_2010_09_16

先问下是否可以这样做触发器插入新建表数据或还有更好更方便的方法吗？

上面触发器把建表和插入的分开测试都可以去执行 ps：当然做插入测试时表是实体表

但放一起统一执行时就报这样的错

服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: 'InOut' 附近有语法错误。

帮忙看看了

里面用到的存储过程也是可以执行的



CREATE procedure CreateTable_ProcessHistory

(@TableName varchar(50),

@TableDate varchar(50) output)

as

declare @sql varchar(1000)

declare @Table varchar(100)

set @TableDate=@TableName+@TableDate

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@TableDate+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin

set @sql='CREATE TABLE [dbo].['+@TableDate+'] (

	[ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,

	[PersonID] [decimal](18, 0) NULL ,

	[MineID] [decimal](18, 0) NULL ,

	[CurrentMinePersonCount] [int] NULL ,

	[ItemCreatedTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[InOutMineMark] [smallint] NULL ,

	[InMineTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[OutMineTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[CurrentAreaID] [int] NULL ,

	[CurrentAreaPersonCount] [int] NULL ,

	[InCurrentAreaTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[CurrentSubstationID] [int] NULL ,

	[CurrentSubstationPersonCount] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[InCurrentSubstationTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[SubstationIDAndTime] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,

	[InsertTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

	[UpdateTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]'

exec (@sql)

set @sql='ALTER TABLE [dbo].['+@TableDate+'] WITH NOCHECK ADD 

CONSTRAINT [PK_'+@TableDate+'] PRIMARY KEY  CLUSTERED 

(

[ID]

)  ON [PRIMARY] '

exec (@sql)

end

GO