寫的觸發器動態生成表，實測了下，沒有生成需要的表，求大神指點下哪個地方有問題
USE [ecology9]
GO
/****** Object: Trigger [dbo].[triggAddMachineInfo] Script Date: 2021/4/27 星期二 15:44:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[triggAddMachineInfo]
ON [dbo].[uf_MachineInfo]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @projid varchar(40),@tablename varchar(100),@MachineNumber varchar(max),@MachineNumber2 varchar(max),@MachineNumber_id2 varchar(100)
select @projid=projid,@MachineNumber=MachineNumber from inserted
if(ISNULL(@projid,'')<>'')
begin
select @tablename='[realnamedata].dbo.LW_Checkin_data_'+@projid
declare @sql varchar(max)
if(OBJECT_ID(@tablename) is null)
begin
select @sql='create table' +@tablename
select @sql=@sql + '([itmid] [int] IDENTITY(1,1) NOT NULL primary key,
[uuid] [varchar](40) NULL,
[deviceId] [nvarchar](40) NULL,
[deviceName] [nvarchar](200) NULL,
[ProjectName] [nvarchar](200) NULL,
[UIO] [int] NULL,
[passtime] [datetime] NULL,
[name] [nvarchar](100) NULL,
[gender] [nvarchar](20) NULL,
[department] [nvarchar](100) NULL,
[idcard] [varchar](40) NULL,
[birthday] [varchar](40) NULL,
[address] [nvarchar](100) NULL,
[temperature] decimal null,
wearmask nvarchar(40) null,
[lat] [varchar](20) NULL,
[lng] [varchar](20) NULL,
[location] [nvarchar](200) NULL,
[addtime] [datetime] NULL,
[ImgData] [varchar](200) NULL)'
select @sql =@sql+' CREATE INDEX index_LW_Checkin_Data_'+@projid + '_uuid ON [lw_checkin].dbo.LW_Checkin_Data_'+@projid + '(uuid)'
select @sql =@sql+' CREATE INDEX index_LW_Checkin_Data_'+@projid + '_idcard ON [lw_checkin].dbo.LW_Checkin_Data_'+@projid +'(idcard)'
select @sql =@sql+' CREATE INDEX index_LW_Checkin_Data_'+@projid + '_uio ON [lw_checkin].dbo.LW_Checkin_Data_'+@projid +'(uio)'
exec(@sql)
end
end
END
uj5u.com熱心網友回復：
報錯了嗎，我猜你create table的后面要加個空格uj5u.com熱心網友回復：
是不是你單次插入多條資料，其中有projid為空的資料，賦值時正好用了這條資料，導致沒走到IF里面去如果是建表陳述句拼接錯誤最后導致建表報錯，難道沒拋錯出來？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/281873.html
標籤：疑難問題
上一篇：佳能列印機