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_RealTimeProcessselect * 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
你的建trigger和测试数据中间放个go,不然 直接一起运行f5,编译器认为创建trigger没有结束
感觉是 触发器 数据插入 新建表的那个 begin end 代码块的问题=。=!
继续找