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

2011-06-20 05:45:35 400 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

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

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



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的情况下再触发

...全文

揭秘MySQL：触发器的全面解析触发器是MySQL中的一种特殊对象，它可以在数据库中的表上定义，用于在表上执行特定的操作。当满足一定的条件时，触发器会自动触发，并执行预定义的操作。本文深入解析了MySQL中的触发器，包括触发器的类型、创建、使用、删除和修改等方面。触发器是MySQL中非常有用的功能，可以在特定的条件下自动执行预定义的操作，实现数据的完整性、一致性和自动化处理。阅读详情

cefriend

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

cefriend 2011-06-21

打赏
举报

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



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);

-晴天 2011-06-20

打赏
举报

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

楼主的问题是:

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

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

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

貌似不是来混分的...

--小F-- 2011-06-20

打赏
举报

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

xuexiaodong2009 2011-06-20

打赏
举报

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

cd731107 2011-06-20

打赏
举报

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

cefriend 2011-06-20

打赏
举报

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

-晴天 2011-06-20

打赏
举报

用 for update

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

相关推荐