34,838
社区成员
发帖
与我相关
我的任务
分享插入时检查改字段是否重复的触发器问题
for insert
as
declare @msg varchar(max)
declare @s varchar(200)
select @s=a from inserted with(nolock)
if
exists(select a from table with(nolock) where a=@s)
begin
select @msg='客户订单号重复'
raiserror(@Msg, 16, 1)
return
end
as
declare @msg varchar(max)
declare @s varchar(200)
select @s=a from inserted with(nolock)
if
exists(select a from table with(nolock) where a=@s)
begin
select @msg='客户订单号重复'
raiserror(@Msg, 16, 1)
return
end
...全文
请发表友善的回复…
发表回复
qq_43940926 2018-12-07
- 打赏
- 举报
就技术方面可以和您沟通一下吗
暴富失败 2018-12-07
- 打赏
- 举报
哈哈，可以啊 真的
zjcxc 元老 2018-12-07
- 打赏
- 举报
[code=sql]
IF EXISTS( SELECT * FROM inserted WHERE a IN( SELECT a FROM table WITH(HOLDLOCK) ) ) -- 与存在的记录重复
OR EXISTS( SELECT a FROM inserted GROUP BY a HAVING COUNT(*) > 1 ) -- 本语句 INSERT 的数据重复
begin
select @msg='客户订单号重复'
raiserror(@Msg, 16, 1)
return
end 
二月十六 版主 2018-12-07
- 打赏
- 举报
把表结构和写的触发器贴上来吧
吉普赛的歌 版主 2018-12-07
- 打赏
- 举报
你触发器的代码再贴一下吧
mzwhq 2018-12-07
- 打赏
- 举报
qq_43540001 2018-12-07
- 打赏
- 举报
好像是不可以的。。
吉普赛的歌 版主 2018-12-07
- 打赏
- 举报
生成 insert 语句：
你可以在 @filter 那里加条件的。
或者直接将20 条数据插入到一个临时表， 直接将那个临时表的数据导出为脚本即可。
如果你有 navicat ， 右键表也可以导出的。
CREATE PROCEDURE [dbo].[Proc_GenerateInsert]
@tablename SYSNAME,
@filter NVARCHAR(500),
@Order NVARCHAR(500)
AS
DECLARE @column VARCHAR(MAX)
DECLARE @columndata VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)
DECLARE @xtype TINYINT
DECLARE @name SYSNAME
DECLARE @objectId INT
DECLARE @objectname SYSNAME
DECLARE @ident INT
SET NOCOUNT ON
SET @objectId = OBJECT_ID(@tablename)
IF @objectId IS NULL -- 判斷對象是否存在
BEGIN
PRINT 'The object not exists'
RETURN
END
SET @objectname = RTRIM(OBJECT_NAME(@objectId))
IF @objectname IS NULL
OR CHARINDEX(@objectname, @tablename) = 0 --此判断不严密
BEGIN
PRINT 'object not in current database'
RETURN
END
IF OBJECTPROPERTY(@objectId, 'IsTable') < > 1 -- 判斷對象是否是table
BEGIN
PRINT 'The object is not table'
RETURN
END
SELECT @ident = STATUS & 0x80
FROM syscolumns
WHERE id = @objectid
AND STATUS & 0x80 = 0x80
IF @ident IS NOT NULL
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
DECLARE syscolumns_cursor CURSOR
FOR
SELECT c.name,
c.xtype
FROM syscolumns c
WHERE c.id = @objectid
ORDER BY
c.colid
OPEN syscolumns_cursor
SET @column = ''
SET @columndata = ''
FETCH NEXT FROM syscolumns_cursor INTO @name,@xtype
WHILE @@fetch_status < > -1
BEGIN
IF @@fetch_status < > -2
BEGIN
IF @xtype NOT IN (189, 34, 35, 99, 98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
BEGIN
SET @column = @column + CASE
WHEN LEN(@column) = 0 THEN ''
ELSE ','
END + '[' + RTRIM(@name) +']'
SET @columndata = @columndata + CASE
WHEN LEN(@columndata) = 0 THEN
''
ELSE ','','','
END
+ CASE
WHEN @xtype IN (167, 175) THEN '''''''''+rtrim([' + @name
+ '])+''''''''' --varchar,char
WHEN @xtype IN (231, 239) THEN '''N''''''+rtrim([' + @name
+ '])+''''''''' --nvarchar,nchar
WHEN @xtype = 61 THEN '''''''''+convert(char(23),[' +
@name + '],121)+''''''''' --datetime
WHEN @xtype = 58 THEN '''''''''+convert(char(16),[' +
@name + '],120)+''''''''' --smalldatetime
WHEN @xtype = 36 THEN '''''''''+convert(char(36),[' +
@name + '])+''''''''' --uniqueidentifier
ELSE '[' + RTRIM(@name) + ']'
END
END
END
FETCH NEXT FROM syscolumns_cursor INTO @name,@xtype
END
CLOSE syscolumns_cursor
DEALLOCATE syscolumns_cursor
SET @sql = 'set nocount on select ''insert ' + @tablename + '(' + @column +
') values(''as ''--'',' + RTRIM(@columndata) + ','')'' from ' + @tablename
IF @filter IS NOT NULL
AND LEN(RTRIM(@filter)) <> 0
SET @sql = @sql + ' where ' + @filter
IF @Order IS NOT NULL
AND LEN(RTRIM(@Order)) <> 0
SET @sql = @sql + ' Order By ' + @Order
PRINT '--' + @sql
EXEC (@sql)
IF @ident IS NOT NULL
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
GOmzwhq 2018-12-07
- 打赏
- 举报
你们加我QQ：2339904，远程求解
mzwhq 2018-12-07
- 打赏
- 举报
我这里，只要你搞else，必然报错
mzwhq 2018-12-07
- 打赏
- 举报
SQL怎么导出数据，你们可以直接语句执行的，我导出一点数据给你们
二月十六 版主 2018-12-07
- 打赏
- 举报
触发器那个else插入的语句，需要补全，由于字段太多，我写的不全，就是测试用的
mzwhq 2018-12-07
- 打赏
- 举报
这个语句目测还是No的
二月十六 版主 2018-12-07
- 打赏
- 举报
测试
第二次插入：
INSERT INTO [dbo].[t_COPD_OrdMst]
(
[fOrdNo],
[fCOrdNo]
)
VALUES
( '1', -- fOrdNo - varchar(50)
N'fff1111111' -- fCOrdNo - nvarchar(30)
)
第二次插入：
INSERT INTO [dbo].[t_COPD_OrdMst]
(
[fOrdNo],
[fCOrdNo]
)
VALUES
( '2', -- fOrdNo - varchar(50)
N'fff1111111' -- fCOrdNo - nvarchar(30)
)
二月十六 版主 2018-12-07
- 打赏
- 举报
alter trigger [dbo].[t_copd_ordmst20181206]
on [dbo].[t_COPD_OrdMst]
INSTEAD OF insert
as
declare @msg varchar(max)
---- if exists(select 1 from t_copd_ordmst with(nolock)
----where fCOrdNo=(select fCOrdNo from inserted with(nolock)))
DECLARE @s VARCHAR(200);
SELECT
@s=fCOrdNo
FROM
inserted WITH (NOLOCK);
IF EXISTS
(
SELECT
fCOrdNo
FROM
t_copd_ordmst WITH (NOLOCK)
WHERE
fCOrdNo = @s
)
BEGIN
SELECT
@msg = '客户订单号重复';
RAISERROR(@msg, 16, 1);
RETURN;
END
ELSE
BEGIN
INSERT INTO [t_COPD_OrdMst](fOrdNo,fCOrdNo) SELECT fOrdNo,fCOrdNo FROM Inserted
END
mzwhq 2018-12-07
- 打赏
- 举报
还不如你直接远程过来
mzwhq 2018-12-07
- 打赏
- 举报
这是表结构，大神们
mzwhq 2018-12-07
- 打赏
- 举报
USE [R02]
GO
/****** Object: Table [dbo].[t_COPD_OrdMst] Script Date: 2018-12-07 15:25:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_COPD_OrdMst](
[fOrdNo] [varchar](50) NOT NULL,
[fOrdType] [varchar](4) NOT NULL,
[fCCode] [varchar](20) NOT NULL,
[fCOrdNo] [nvarchar](30) NULL,
[fSalesManCode] [varchar](200) NULL,
[fSalesMan] [nvarchar](200) NULL,
[fFollowManCode] [varchar](200) NULL,
[fFollowMan] [nvarchar](200) NULL,
[fCryCode] [char](3) NOT NULL,
[fRate] [decimal](28, 8) NOT NULL,
[fPayCode] [varchar](4) NOT NULL,
[fPriceCode] [varchar](4) NOT NULL,
[fOrdRdate] [datetime] NOT NULL CONSTRAINT [DF_t_copd_ordmst_fOrdRdate] DEFAULT (getdate()),
[fOrdStDate] [smalldatetime] NULL,
[fOrdEndDate] [smalldatetime] NULL,
[fEstStDate] [smalldatetime] NULL,
[fEstEndDate] [smalldatetime] NULL,
[fReqStDate] [smalldatetime] NULL,
[fReqEndDate] [smalldatetime] NULL,
[fCfmStDate] [smalldatetime] NULL,
[fCfmEndDate] [smalldatetime] NULL,
[fPgDate] [smalldatetime] NULL,
[fIfMPS] [char](1) NOT NULL,
[fIfCrlStkByOrd] [char](1) NOT NULL,
[fProNtfFlag] [char](1) NOT NULL CONSTRAINT [DF_t_COPD_OrdMst_fProNtfFlag] DEFAULT ('0'),
[fOutFlag] [char](1) NOT NULL,
[fIfOfferCO] [char](1) NOT NULL,
[fIfDisinfect] [char](1) NULL,
[fOriType] [char](1) NOT NULL,
[fProdReq] [nvarchar](2000) NULL,
[fEdition] [int] NULL,
[fAttachment] [int] NOT NULL,
[fIfMRP] [char](1) NOT NULL,
[fIfForecast] [char](1) NOT NULL,
[fLoadPort] [nvarchar](200) NULL,
[fDestPort] [nvarchar](200) NULL,
[fDischargePort] [nvarchar](200) NULL,
[fClosePort] [nvarchar](200) NULL,
[fForwarder] [nvarchar](50) NULL,
[fShipper] [nvarchar](200) NULL,
[fConsignee] [nvarchar](200) NULL,
[fShipTo] [nvarchar](200) NULL,
[fNotifyParty] [nvarchar](300) NULL,
[fAlsoNotify] [nvarchar](300) NULL,
[fShipMark] [nvarchar](1000) NULL,
[fSideMark] [nvarchar](1000) NULL,
[fShippingPort] [nvarchar](200) NULL,
[fRemark] [nvarchar](1500) NULL,
[fCFlag] [char](1) NOT NULL CONSTRAINT [DF__t_COPD_Or__fCFla__14B1DB51] DEFAULT ((4)),
[fCreatorID] [varchar](20) NULL,
[fCreator] [nvarchar](30) NULL,
[fCDate] [datetime] NULL CONSTRAINT [DF_t_copd_ordmst_fCDate] DEFAULT (getdate()),
[fModifierID] [varchar](20) NULL,
[fModifier] [nvarchar](30) NULL,
[fModiDate] [datetime] NULL,
[fApproverID] [varchar](20) NULL,
[fApprover] [nvarchar](30) NULL,
[fAppDate] [datetime] NULL,
[fDCWFSimplelog] [nvarchar](max) NULL,
[fIfCancel] [char](1) NOT NULL,
[fCancelID] [varchar](20) NULL,
[fCanceler] [nvarchar](30) NULL,
[fCancelDate] [datetime] NULL,
[fIfClose] [char](1) NOT NULL,
[fCloserID] [varchar](20) NULL,
[fCloser] [nvarchar](30) NULL,
[fCloseDate] [datetime] NULL,
[fAcceptCpyCode] [varchar](10) NULL,
[fRebateAmt] [decimal](18, 6) NOT NULL CONSTRAINT [DF_t_COPD_OrdMst_fRebateAmt] DEFAULT ((0)),
[fSharedIndex] [smallint] NOT NULL,
[_CfTerminalCst] [nvarchar](100) NULL,
[fIfDlvReflashPrice] [char](1) NOT NULL,
[fTimeStamp] [timestamp] NOT NULL,
[fSpAppFlag] [char](1) NOT NULL,
[fSpAppReqerID] [varchar](20) NULL,
[fSpAppReqer] [nvarchar](30) NULL,
[fSpAppReqDate] [datetime] NULL,
[fSpApperID] [varchar](20) NULL,
[fSpApper] [nvarchar](30) NULL,
[fSpAppDate] [datetime] NULL,
[fDeptId] [int] NULL,
[fMKCode] [varchar](2) NULL,
[fTransitPrvCode] [varchar](10) NULL,
[fTransitCode] [varchar](10) NULL,
[fCntQty] [decimal](18, 6) NULL,
[fOrdAllamt] [decimal](18, 6) NULL,
[fSelAllamt] [decimal](18, 6) NULL,
[fUpdateDate] [datetime] NULL,
[fDCSPSimplelog] [nvarchar](max) NULL,
[fSendAddress] [nvarchar](400) NULL,
[fSendZoneCode] [varchar](10) NULL,
[fSendStation] [nvarchar](100) NULL,
[fIfSend] [char](1) NULL,
[fSendFee] [decimal](18, 6) NULL CONSTRAINT [DF_t_copd_ordmst_fSendFee] DEFAULT ((0)),
[fTotalPcsFee] [decimal](18, 6) NULL CONSTRAINT [DF_t_copd_ordmst_fTotalPcsFee] DEFAULT ((0)),
[fTotalM3CubFee] [decimal](18, 6) NULL CONSTRAINT [DF_t_copd_ordmst_fTotalM3CubFee] DEFAULT ((0)),
[fTotalTWeightFee] [decimal](18, 6) NULL CONSTRAINT [DF_t_copd_ordmst_fTotalTWeightFee] DEFAULT ((0)),
[fLogisActAllPcs] [decimal](18, 6) NULL,
[fLogisActAllM3Cub] [decimal](18, 6) NULL,
[fLogisActAllGrossWeight] [decimal](18, 6) NULL,
[fReDisRate] [decimal](18, 6) NULL CONSTRAINT [DF_t_copd_ordmst_fReDisRate] DEFAULT ((100)),
[fSendProvinceCode] [varchar](10) NULL,
[fSendCityCode] [varchar](8) NULL CONSTRAINT [DF_t_copd_ordmst_fSendCityCode] DEFAULT (''),
[fLogisRemark] [nvarchar](2000) NULL CONSTRAINT [DF_t_copd_ordmst_fLogisRemark] DEFAULT (''),
[fLogisPcsUP] [decimal](18, 6) NULL,
[fLogisCubUP] [decimal](18, 6) NULL,
[fLogisWeightUP] [decimal](18, 6) NULL,
[fLogisActFeeFlag] [char](1) NULL,
[fRebateShowAmt] [decimal](18, 6) NULL,
[fDlvAmt] [decimal](18, 6) NULL,
[fPrjNo] [varchar](50) NOT NULL,
[fRmDDate] [smalldatetime] NULL,
[fMrpPauserID] [varchar](20) NULL,
[fMrpPauser] [nvarchar](30) NULL,
[fMrpPauseDate] [datetime] NULL,
[frmStdate] [smalldatetime] NULL,
[frmEnddate] [smalldatetime] NULL,
[fSendDistrictCode] [varchar](20) NOT NULL DEFAULT ('****'),
[fCtnCode] [varchar](10) NULL,
[fInfoFee] [decimal](18, 6) NOT NULL,
[foilFee] [decimal](18, 6) NOT NULL,
[fAsureFee] [decimal](18, 6) NOT NULL,
[fAsureAmt] [decimal](18, 6) NOT NULL,
[fdeliverFee] [decimal](18, 6) NOT NULL,
[fAsureRate] [decimal](18, 6) NOT NULL,
[fdeliverTotal] [decimal](18, 6) NOT NULL,
[fLogiFeeMethod] [char](1) NULL,
[fSendFeeMethod] [char](1) NULL,
[fProdStatus] [char](1) NOT NULL DEFAULT ('0'),
[fcontractestStdate] [datetime] NULL,
[fcontractestEnddate] [datetime] NULL,
[fDeliveryStationID] [int] NOT NULL DEFAULT ((0)),
[fMinPrice] [decimal](18, 6) NOT NULL,
[fPackReq] [nvarchar](1000) NULL,
[_x_f001] [nvarchar](4000) NULL,
[fifdistighten] [char](1) NOT NULL DEFAULT ('0'),
[fIfEBSConfirm] [char](1) NOT NULL,
[fTaskAddress] [nvarchar](2000) NOT NULL,
[fbulkMinAmt] [decimal](18, 6) NOT NULL DEFAULT ((0)),
[fTaskMinAmt] [decimal](18, 6) NOT NULL DEFAULT ((0)),
[fProvinceCode_GX] [varchar](10) NOT NULL,
[fCityCode_GX] [varchar](8) NOT NULL,
[fDistrictCode_GX] [varchar](10) NOT NULL,
[fProvinceCode_ZX] [varchar](10) NOT NULL,
[fCityCode_ZX] [varchar](8) NOT NULL,
[fDistrictCode_ZX] [varchar](10) NOT NULL,
[fTaskDestination_ZX] [nvarchar](300) NOT NULL,
[fTaskCubUp] [decimal](18, 6) NOT NULL DEFAULT ((0)),
[fDryAddress] [nvarchar](2000) NOT NULL,
[fCOrdType] [char](1) NOT NULL,
[fPGPointCode] [varchar](10) NULL,
[fIfPreSale] [char](1) NOT NULL,
[fOrdClass] [char](1) NOT NULL,
[fROSchemeCode] [varchar](10) NOT NULL,
[fIfShopDlv] [char](1) NOT NULL CONSTRAINT [DF_t_copd_ordmst_fIfShopDlv] DEFAULT ((0)),
[fMarketNo] [nvarchar](50) NULL,
[fDeliveryWay] [char](1) NOT NULL CONSTRAINT [DF_t_copd_ordmst_fDeliveryWay] DEFAULT ('2'),
[fdirectPur] [char](1) NOT NULL,
[fdlvpur] [char](1) NOT NULL,
[fSourceType] [char](1) NOT NULL,
[fPrvCode] [varchar](20) NOT NULL,
CONSTRAINT [PK_t_COPD_OrdMst] PRIMARY KEY CLUSTERED
(
[fOrdNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fOrdType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接单日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fOrdRdate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fOriType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产要求' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fProdReq'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ship Mark' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fShipMark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Side Mark' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fSideMark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fCDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料交期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fRmDDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'投产状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fProdStatus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发货站点ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fDeliveryStationID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最低运费' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fMinPrice'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'包装要求' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_COPD_OrdMst', @level2type=N'COLUMN',@level2name=N'fPackReq'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'整单折扣是否折算明细' , @level0type=N'SCHEMA',@leve吉普赛的歌 版主 2018-12-07
- 打赏
- 举报
表结构脚本， 还有测试数据也加一些吧。
mzwhq 2018-12-07
- 打赏
- 举报
真他娘的神奇。各位的脚本都是没问题的，但是，在我这就是不行
加载更多回复（19）
