create trigger tt on BOM_027 for insert,update as begin if exists(select 1 from test_jiekou as b ,inserted as i where left(b.testweiyiid,2)=left(i.PARTID,2)) update a set PARTFIXEDBACK3=b.CTYPEID from BOM_027 as a join inserted as i on a.CTYPEID=i.CTYPEID join BOM_083 as b on a.CTYPEID=b.PARTID end
on B
after insert
as
....
最好贴出表结构,和比较时 的关联字段。
set b=b.b
from a join b on a=left(b,2)
create trigger b_insupd
on B
for insert,update
as
begin
update A set B=i.B from inserted i
where left(A.A,2)=left(i.B,2);
end
go
第一张表:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BOM_083](
[CTYPEID] [varchar](50) NOT NULL,
[CPARENTID] [varchar](50) NOT NULL,
[CTYPENAME] [varchar](100) NOT NULL,
[CTYPEKEY] [varchar](50) NOT NULL,
[CISCHILD] [varchar](50) NULL,
[CHILDLENGTH] [varchar](50) NULL,
[CFLOWSTART] [varchar](50) NULL,
[CFLOWLENGTH] [varchar](50) NULL,
[CJCTYPEKEYID] [varchar](100) NOT NULL,
[CTYPEBACKUP1] [varchar](100) NULL,
[CTYPEBACKUP2] [varchar](100) NULL,
[CTYPEBACKUP3] [varchar](100) NULL,
[RULEID] [int] NULL CONSTRAINT [DF_BOM_083_RULEID] DEFAULT ((-1)),
[typerole] [varchar](1000) NULL,
CONSTRAINT [PK_BOM_083] PRIMARY KEY CLUSTERED
(
[CTYPEID] 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]GO
SET ANSI_PADDING OFF第二张表:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test_jiekou](
[testname] [varchar](100) NOT NULL,
[testweiyiid] [varchar](100) NOT NULL,
CONSTRAINT [PK_test_jiekou] PRIMARY KEY CLUSTERED
(
[testname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF第三张表:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BOM_027](
[PARTID] [varchar](100) NOT NULL,
[PARTVAR] [varchar](100) NOT NULL,
[KIND] [varchar](100) NULL CONSTRAINT [DF_BOM_027_KIND] DEFAULT ('零件'),
[SOURCE] [varchar](100) NULL CONSTRAINT [DF_BOM_027_SOURCE] DEFAULT ('自制件'),
[KEYIMPORTANT] [varchar](100) NULL CONSTRAINT [DF_BOM_027_KEYIMPORTANT] DEFAULT ('不是'),
[MATERIAL] [varchar](100) NULL,
[WEIGHT] [varchar](100) NULL,
[COSTPRICE] [varchar](200) NULL,
[ARCHIVESTATE] [varchar](100) NULL,
[CHINADGREE] [varchar](100) NULL,
[CHINANAME] [nvarchar](200) NULL,
[COUNTS] [varchar](100) NULL CONSTRAINT [DF_BOM_027_COUNTS] DEFAULT (1),
[ENGLISHNAME] [varchar](100) NULL,
[SPECIALITYOFFICE] [varchar](100) NULL,
[COMPAREPART] [varchar](3000) NULL,
[FOREIGNPARTID] [varchar](100) NULL,
[ORIGINALFIGID] [varchar](100) NULL,
[STANDARDFIGID] [varchar](100) NULL,
[DESIGNDATE] [varchar](50) NULL,
[CHECKER] [varchar](100) NULL,
[AUDITOR] [varchar](100) NULL,
[PROCESSCHECKER] [varchar](100) NULL,
[CRITERION] [varchar](100) NULL,
[AFFIRMANT] [varchar](100) NULL,
[AFFIRMDATE] [varchar](50) NULL,
[FIGDIMENSION] [varchar](100) NULL,
[SCALE] [varchar](100) NULL,
[FIGMARK] [varchar](100) NULL,
[CODER] [varchar](100) NULL,
[CODEDATE] [varchar](50) NULL,
[CODESTATE] [varchar](100) NULL,
[MATUREDEGREE] [varchar](100) NULL,
[VALIDATETIME] [varchar](50) NULL,
[INVALIDATETIME] [varchar](50) NULL,
[FUNCTIONID] [varchar](50) NULL,
[PARTFIXEDBACK1] [varchar](100) NULL,
[PARTFIXEDBACK2] [varchar](3000) NULL,
[PARTFIXEDBACK3] [varchar](100) NULL,
[PARTFIXEDBACK4] [varchar](100) NULL CONSTRAINT [DF_BOM_027_PARTFIXEDBACK4] DEFAULT ('1'),
[PARTFIXEDBACK5] [varchar](100) NULL,
[SPEC] [varchar](200) NULL,
[MODEL] [nvarchar](200) NULL,
[PARTPICFTPNAME] [varchar](500) NULL,
[ENGNAME] [varchar](100) NULL,
[PARTSTATE] [varchar](50) NULL,
[PRODUCTKIND] [varchar](50) NULL,
[CITATIONCOUNT] [int] NULL DEFAULT (0)
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BOM_027] ADD [INPUTERP] [char](1) NOT NULL DEFAULT ('0')
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BOM_027] ADD [control] [varchar](100) NULL
ALTER TABLE [dbo].[BOM_027] ADD [comefrom] [varchar](100) NULL
ALTER TABLE [dbo].[BOM_027] ADD [SERIESNAME] [varchar](100) NULL
ALTER TABLE [dbo].[BOM_027] ADD [ERPInventoryCode] [varchar](200) NULL
/****** 对象: Index [PK_PARTFIXED] 脚本日期: 10/23/2012 11:11:04 ******/
ALTER TABLE [dbo].[BOM_027] ADD CONSTRAINT [PK_PARTFIXED] PRIMARY KEY CLUSTERED
(
[PARTID] ASC,
[PARTVAR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]GO
SET ANSI_PADDING OFF我的意思是:
当表BOM_027的列PARTID中的值前2位与表test_jiekou的列testweiyiid中的值前2位相等时候,就把表BOM_083的列CTYPEID的值赋值给表BOM_027的列PARTFIXEDBACK3。
换句话说:就是每当表BOM_027中增加一条记录,数据库会比较一下:“表BOM_027的列PARTID中的值前2位与表test_jiekou的列testweiyiid中的值前2位相等”是否相等。相等就把:“表BOM_083的列CTYPEID的值赋值给表BOM_027的列PARTFIXEDBACK3”。
for insert,update
as
begin
if exists(select 1 from test_jiekou as b ,inserted as i where left(b.testweiyiid,2)=left(i.PARTID,2))
update a set PARTFIXEDBACK3=b.CTYPEID
from BOM_027 as a
join inserted as i on a.CTYPEID=i.CTYPEID
join BOM_083 as b on a.CTYPEID=b.PARTID
end
列名 'CTYPEID' 无效。
消息 207,级别 16,状态 1,过程 tt,第 8 行
列名 'CTYPEID' 无效。
消息 207,级别 16,状态 1,过程 tt,第 9 行
列名 'CTYPEID' 无效。
消息 207,级别 16,状态 1,过程 tt,第 9 行
列名 'PARTID' 无效。