求教:触发器根据另一个表某个字段变化触发~~~~~~~~

[cefriend]

触发器根据另一个表某个字段变化触发 这种触发器大家写过吗........

下面的例子编辑没有出错,但不触发

create trigger [dbo].[trg_insert_linkman] on [LinkMan]
for insert
as
insert into CommitJob(ExecSQL) --------**这里的记录表,SQL语句字段名要根据实际情况修改
select 'insert [LinkMan]([LinkManCode],[ParentID],[Parent],[Name],[Nature],[Tel],[Mobile],[EMail],[IsManContact],[IM])
values('+case when [LinkManCode] is null then 'NULL' else ''''+replace([LinkManCode],'''','''''')+'''' end+','+case when [ParentID] is null then 'NULL' else ''''+replace([ParentID],'''','''''')+'''' end+','+case when [Parent] is null then 'NULL' else ''''+replace([Parent],'''','''''')+'''' end+','+case when [Name] is null then 'NULL' else ''''+replace([Name],'''','''''')+'''' end+','+case when [Nature] is null then 'NULL' else ''''+replace([Nature],'''','''''')+'''' end+','+case when [Tel] is null then 'NULL' else ''''+replace([Tel],'''','''''')+'''' end+','+case when [Mobile] is null then 'NULL' else ''''+replace([Mobile],'''','''''')+'''' end+','+case when [EMail] is null then 'NULL' else ''''+replace([EMail],'''','''''')+'''' end+','+case when [IsManContact] is null then 'NULL' else ''''+replace([IsManContact],'''','''''')+'''' end+','+case when [IM] is null then 'NULL' else ''''+replace([IM],'''','''''')+'''' end+')' 
from inserted,Cust_CustInfo where inserted.parentID = Cust_CustInfo.CustCode and Cust_CustInfo.CustStatus='1'---**表Cust_CustInfo表中字段CustStatus=1的情况下再触发

[-晴天]

用 for update

[cefriend]

楼上是混分的吧.这种回答跟没有回答一样

[cd731107]

触发器只能是根据本表的字段发生变化时，才能触发，除非是本表触发器修改了其他表，
引起其他表的触发器进行触发

[xuexiaodong2009]

for insert
只有插入数据时才出发的，更新是不触发

[--小F--]

create trigger [dbo].[trg_insert_linkman] on [LinkMan]
for insert,update
as
if update(某列)
begin
insert into CommitJob(ExecSQL) --------**这里的记录表,SQL语句字段名要根据实际情况修改
select 'insert [LinkMan]([LinkManCode],[ParentID],[Parent],[Name],[Nature],[Tel],[Mobile],[EMail],[IsManContact],[IM])
values('+case when [LinkManCode] is null then 'NULL' else ''''+replace([LinkManCode],'''','''''')+'''' end+','+case when [ParentID] is null then 'NULL' else ''''+replace([ParentID],'''','''''')+'''' end+','+case when [Parent] is null then 'NULL' else ''''+replace([Parent],'''','''''')+'''' end+','+case when [Name] is null then 'NULL' else ''''+replace([Name],'''','''''')+'''' end+','+case when [Nature] is null then 'NULL' else ''''+replace([Nature],'''','''''')+'''' end+','+case when [Tel] is null then 'NULL' else ''''+replace([Tel],'''','''''')+'''' end+','+case when [Mobile] is null then 'NULL' else ''''+replace([Mobile],'''','''''')+'''' end+','+case when [EMail] is null then 'NULL' else ''''+replace([EMail],'''','''''')+'''' end+','+case when [IsManContact] is null then 'NULL' else ''''+replace([IsManContact],'''','''''')+'''' end+','+case when [IM] is null then 'NULL' else ''''+replace([IM],'''','''''')+'''' end+')' 
from inserted,Cust_CustInfo where inserted.parentID = Cust_CustInfo.CustCode and Cust_CustInfo.CustStatus='1'---**表Cust_CustInfo表中字段CustStatus=1的情况下再触发
end

[-晴天]

[Quote=引用 2 楼 cefriend 的回复:]
楼上是混分的吧.这种回答跟没有回答一样
[/Quote]

楼主的问题是:

触发器根据另一个表某个字段变化触发 这种触发器大家写过吗........

字段值变化而触发,这是基于 update 操作的触发器,可楼主的程序是:
create trigger [dbo].[trg_insert_linkman] on [LinkMan]
for insert
as

这个要改成 1 楼所写的:
for update

貌似不是来混分的...

[cefriend]

求人不求自已......................没一个人明白

ALTER trigger [dbo].[9E48270F-805F-46CE-9F70-27628F9EAE73] on [dbo].[Cust_CustInfo]
for update
as
declare @ExecSQL varchar(2000),
		 @LinkManCode varchar(50),
			 @ParentID varchar(50),
				 @Parent varchar(50),
					 @Name varchar(50),
						 @Nature varchar(50),
							@Tel varchar(50),
								@Mobile varchar(50);
insert into CommitJob(ExecSQL) --------**这里的记录表,SQL语句字段名要根据实际情况修改
select 'insert [Cust_CustInfo]([ServiceCode],[CustName],[CustCode],[Address],[CommAddr],[WebAddr],[ComponyDate],[Fax],[CompanyTel],[PostCode],[TelTraffic],[ProPrin],[BusPrin],[AgentPrin],[SalesModel],[CustStatus],[EstiSignDate],[Note],[Operator],[OwnerID],[Principal],[UserID],[CreatUser],[CreatDate],[UpDataUser],[UpDataDate],[CreatName],[State],[City],[CustLevel],[CustEmail],[IsInvoice],[InvoiceName],[InvoiceAddress])
values('+case when [ServiceCode] is null then 'NULL' else ''''+replace([ServiceCode],'''','''''')+'''' end+','+case when [CustName] is null then 'NULL' else ''''+replace([CustName],'''','''''')+'''' end+','+case when [CustCode] is null then 'NULL' else ''''+replace([CustCode],'''','''''')+'''' end+','+case when [Address] is null then 'NULL' else ''''+replace([Address],'''','''''')+'''' end+','+case when [CommAddr] is null then 'NULL' else ''''+replace([CommAddr],'''','''''')+'''' end+','+case when [WebAddr] is null then 'NULL' else ''''+replace([WebAddr],'''','''''')+'''' end+','+case when [ComponyDate] is null then 'NULL' else ''''+replace(convert(varchar,[ComponyDate]),'''','''''')+'''' end+','+case when [Fax] is null then 'NULL' else ''''+replace([Fax],'''','''''')+'''' end+','+case when [CompanyTel] is null then 'NULL' else ''''+replace([CompanyTel],'''','''''')+'''' end+','+case when [PostCode] is null then 'NULL' else ''''+replace([PostCode],'''','''''')+'''' end+','+case when [TelTraffic] is null then 'NULL' else convert(varchar,[TelTraffic]) end+','+case when [ProPrin] is null then 'NULL' else convert(varchar,[ProPrin]) end+','+case when [BusPrin] is null then 'NULL' else convert(varchar,[BusPrin]) end+','+case when [AgentPrin] is null then 'NULL' else convert(varchar,[AgentPrin]) end+','+case when [SalesModel] is null then 'NULL' else ''''+replace([SalesModel],'''','''''')+'''' end+','+case when [CustStatus] is null then 'NULL' else convert(varchar,[CustStatus]) end+','+case when [EstiSignDate] is null then 'NULL' else ''''+replace(convert(varchar,[EstiSignDate]),'''','''''')+'''' end+','+case when [Note] is null then 'NULL' else ''''+replace([Note],'''','''''')+'''' end+','+case when [Operator] is null then 'NULL' else ''''+replace([Operator],'''','''''')+'''' end+','+case when [OwnerID] is null then 'NULL' else ''''+replace([OwnerID],'''','''''')+'''' end+','+case when [Principal] is null then 'NULL' else ''''+replace([Principal],'''','''''')+'''' end+','+case when [UserID] is null then 'NULL' else ''''+replace([UserID],'''','''''')+'''' end+','+case when [CreatUser] is null then 'NULL' else ''''+replace([CreatUser],'''','''''')+'''' end+','+case when [CreatDate] is null then 'NULL' else ''''+replace(convert(varchar,[CreatDate]),'''','''''')+'''' end+','+case when [UpDataUser] is null then 'NULL' else ''''+replace([UpDataUser],'''','''''')+'''' end+','+case when [UpDataDate] is null then 'NULL' else ''''+replace(convert(varchar,[UpDataDate]),'''','''''')+'''' end+','+case when [CreatName] is null then 'NULL' else ''''+replace([CreatName],'''','''''')+'''' end+','+case when [State] is null then 'NULL' else convert(varchar,[State]) end+','+case when [City] is null then 'NULL' else convert(varchar,[City]) end+','+case when [CustLevel] is null then 'NULL' else convert(varchar,[CustLevel]) end+','+case when [CustEmail] is null then 'NULL' else ''''+replace([CustEmail],'''','''''')+'''' end+','+case when [IsInvoice] is null then 'NULL' else ''''+replace([IsInvoice],'''','''''')+'''' end+','+case when [InvoiceName] is null then 'NULL' else ''''+replace([InvoiceName],'''','''''')+'''' end+','+case when [InvoiceAddress] is null then 'NULL' else ''''+replace([InvoiceAddress],'''','''''')+'''' end+')' 
from inserted where CustStatus='1'

select  @LinkManCode=lm.LinkManCode, 
		@ParentID=lm.ParentID,
		@Parent=lm.Parent,
		@Name=lm.Name,
		@Nature=lm.Nature,
		@Tel=lm.Tel,
		@Mobile=lm.Mobile
from inserted ins inner join LinkMan lm on ins.CustCode=lm.ParentID where ins.CustStatus='1';
print @LinkManCode
print @ParentID
print @Name

set @ExecSQL = 'insert into LinkMan(LinkManCode, ParentID, Parent, Name, Nature, Tel, Mobile) '+
               ' values('''+isnull(convert(varchar(50),@LinkManCode),'')+''','''+isnull(convert(varchar(50),@ParentID),'')+''','''+isnull(convert(varchar(50),@Parent),'')+''','''+isnull(convert(varchar(50),@Name),'')+''','''+isnull(convert(varchar(50),@Nature),'')+''','''+isnull(convert(varchar(50),@Tel),'')+''','''+isnull(convert(varchar(50),@Mobile),'')+''')';
print @ExecSQL
if(@ExecSQL!='')
insert into CommitJob (InsertTime, ExecSQL, TransFlag) values(getdate(), @ExecSQL, 0);