触发接受多个值

本文介绍了触发接受多个值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个放在表A上的触发器。 每当更新表A的值时，触发更新表B和表C. 但是当我批量更新表A时，表B和表C中的第一个值都会更新。 他们是触发器可以接受多次更新并执行其余任何操作的方式。 ALTER TRIGGER [dbo]。[selfbill] on [dbo]。[tblSelfBill] for update AS 声明 @ selfbillid int ; 声明 @ TotalFreeTextCommission decimal （ 18 ， 8 ）; 声明 @ Free_Text nvarchar （ 100 ）; 选择 @ selfbillid = i.selfbillid 来自插入i; 选择 @ TotalFreeTextCommission = i.totalfreetextcommission 来自插入i; 如果 update （TotalFreeTextCommission） set @Free_Text = ' 新委员会' 如果 存在（选择 * 来自 dbo.tblSelfBillDetail 其中 SelfBillID = @ selfbillid 和 NoOfConnectionBF null 和 NoOfConnectionSold null 和 NoOfConnectionActivated null 和 NoOfConnectionCF null 和 NoOfConnectionReturned null 和 ActivationPercentage null ） update tblSelfBillDetail set Commision = @ TotalFreeTextCommission 其中 selfbillid = @ selfbillid 和 NoOfConnectionBF null 和 NoOfConnectionSold null 和 NoOfConnectionActivated null 和 NoOfConnectionCF null 和 NoOfConnectionReturned null 和 ActivationPercentage null else insert into tblSelfBillDetail - （Selfbillid，sectionID，NetworkID，描述，Commision，NoOfConnectionBF，NoOfConnectionSold，NoOfConnectionActivated，NoOfConnectionCF，NoOfConnectionReturned，ActivationPercentage） 值（ @ selfbillid ， nu ll ， null ， @ Free_Text ， @ TotalFreeTextCommission ， null ， null ， null ， null ， null ， null ） 如果 存在（选择 * 来自 dbo.tblSelfBillFreeText 其中​​ SelfBillID = @ selfbillid ） update tblselfbillfreetext set commission = @ TotalFreeTextCommission，description = @Free_Text 其中 selfbillid = @ selfbillid else insert into tblselfbillfreetext values （ @ selfbillid ， @Free_Text ， @ TotalFreeTextCommission ） 解决方案 您只处理插入表中的单个记录，例如看看从插入的i中选择@selfbillid = i.selfbillid; 该表中可以有多条记录 - 特别是在批量更新中。 您需要使用游标逐步执行表格（不要这样做！）或者找到一种方法来加入表格来进行更新或者找到完全相同的替代方法。 以下资源可能有用： msdn文章 [ ^ ] 以正确的方式编写触发器 [ ^ ] DBA ramblings [ ^ ] 就个人而言，我会有一个存储过程来对所有表进行更新 - 触发器对性能非常不利（特别是如果你使用游标 - yuk！） 这应该可以解决问题： ALTER TRIGGER [dbo]。[selfbill] ON [dbo] 。[tblSelfBill] FOR UPDATE 作为 BEGIN DECLARE @Free_Text nvarchar （ 100 ）; SET NOCOUNT ON ; 如果 更新（TotalFreeTextCommission） SET @Free_Text = ' 新委员会'; 更新 D SET Commission = I.TotalFreeTextCommission FROM 插入 As I INNER JOIN dbo.tblSelfBillDetail As D ON D.SelfBillID = I.SelfBillID WHERE D. NoOfConnectionBF Null AND D.NoOfConnectionSold Null AND D.NoOfConnectionActivated Null AND D.NoOfConnectionCF Null AND D.NoOfConnectionReturned 空 AND D.ActivationPercentage Null ; INSERT INTO dbo.tblSelfBillDetail （ SelfBillID， AectionID， NetworkID，描述，佣金， NoOfConnectionBF， NoOfConnectionSold， NoOfConnectionActivated， NoOfConnectionCF， NoOfConnectionReturned， ActivationPercentage ） SELECT I.SelfBillID， Null ， Null ， @ Free_Text ， I.TotalFreeTextCommission， Null ， Null ， Null ， Null ， Null ， Null FROM 插入 As I WHERE 不 存在 （ SELECT 1 FROM dbo.tblSelfBillDetail As D WHERE D.SelfBillID = I.SelfBillID AND D.NoOfConnectionBF Null AND D.NoOfConnectionSold Null AND D.NoOfConn ectionActivated Null AND D.NoOfConnectionCF Null AND D.NoOfConnectionReturned Null AND D.ActivationPercentage Null ）; 更新 我 SET Commission = I.TotalFreeTextCommission， Description = @ Free_Text FROM $ b插入$ b 作为 I INNER JOIN dbo.tblSelfBillFreeText As T ON T.SelfBillID = I.SelfBillID ; INSERT INTO dbo.tblSelfBillFreeText （ SelfBillID，佣金，描述） SELECT SelfBillID， TotalFreeTextCommission， @ Free_Text FROM 插入作为 I WHERE 不 存在 （ SELECT 1 FROM dbo.tblSelfBillFreeText 作为 T WHERE T.SelfBillID = I.SelfBillID ）; END 在SQL Server中，每个语句触发一次。 你可以根据你的要求在下面试试。 1）如果可能的话修改触发器来处理所有一次性行或 2）将数据导入临时表，执行所需的活动（再次设置）并将其移至最终表。 I have a trigger which is placed on table A.Whenever a value of table A is updated trigger updates Table B and Table C.But when I bulk update table A only first value in both Table B and Table C are updated.Is their any way wherein the trigger can accept those multiple update and perform the rest.[EDIT - OP Code from comments]ALTER TRIGGER [dbo].[selfbill] on [dbo].[tblSelfBill] for update AS declare @selfbillid int; declare @TotalFreeTextCommission decimal(18,8); declare @Free_Text nvarchar(100); select @selfbillid = i.selfbillid from inserted i; select @TotalFreeTextCommission = i.totalfreetextcommission from inserted i; if update(TotalFreeTextCommission) set @Free_Text= 'New Commission' if exists (select * from dbo.tblSelfBillDetail where SelfBillID = @selfbillid and NoOfConnectionBF is null and NoOfConnectionSold is null and NoOfConnectionActivated is null and NoOfConnectionCF is null and NoOfConnectionReturned is null and ActivationPercentage is null) update tblSelfBillDetail set Commision=@TotalFreeTextCommission where selfbillid=@selfbillid and NoOfConnectionBF is null and NoOfConnectionSold is null and NoOfConnectionActivated is null and NoOfConnectionCF is null and NoOfConnectionReturned is null and ActivationPercentage is null else insert into tblSelfBillDetail --(Selfbillid,sectionID,NetworkID,Description,Commision,NoOfConnectionBF,NoOfConnectionSold,NoOfConnectionActivated,NoOfConnectionCF,NoOfConnectionReturned,ActivationPercentage) values (@selfbillid,null,null,@Free_Text,@TotalFreeTextCommission,null,null,null,null,null,null) if exists (select * from dbo.tblSelfBillFreeText where SelfBillID = @selfbillid ) update tblselfbillfreetext set commission=@TotalFreeTextCommission,description =@Free_Text Where selfbillid = @selfbillid else insert into tblselfbillfreetext values (@selfbillid,@Free_Text,@TotalFreeTextCommission) 解决方案 You are only handling a single record from the inserted table e.g. look at select @selfbillid = i.selfbillid from inserted i;There can be more than 1 record in that table - especially in a bulk update.You either need to use a cursor to step through the table (don't do this!) or find a way to join on the table to do the updates OR find an alternative way of doing this altogether.The following resources might be useful:msdn article[^]writing triggers in the right way[^]DBA ramblings[^]Personally I would have a stored procedure to do the updates to all of the tables - triggers are quite bad for performance (especially if you do use a cursor - yuk!)This should do the trick:ALTER TRIGGER [dbo].[selfbill]ON [dbo].[tblSelfBill]FOR UPDATEAsBEGINDECLARE @Free_Text nvarchar(100); SET NOCOUNT ON; If Update(TotalFreeTextCommission) SET @Free_Text= 'New Commission'; UPDATE D SET Commission = I.TotalFreeTextCommission FROM inserted As I INNER JOIN dbo.tblSelfBillDetail As D ON D.SelfBillID = I.SelfBillID WHERE D.NoOfConnectionBF Is Null AND D.NoOfConnectionSold Is Null AND D.NoOfConnectionActivated Is Null AND D.NoOfConnectionCF Is Null AND D.NoOfConnectionReturned Is Null AND D.ActivationPercentage Is Null ; INSERT INTO dbo.tblSelfBillDetail ( SelfBillID, AectionID, NetworkID, Description, Commission, NoOfConnectionBF, NoOfConnectionSold, NoOfConnectionActivated, NoOfConnectionCF, NoOfConnectionReturned, ActivationPercentage ) SELECT I.SelfBillID, Null, Null, @Free_Text, I.TotalFreeTextCommission, Null, Null, Null, Null, Null, Null FROM inserted As I WHERE Not Exists ( SELECT 1 FROM dbo.tblSelfBillDetail As D WHERE D.SelfBillID = I.SelfBillID AND D.NoOfConnectionBF Is Null AND D.NoOfConnectionSold Is Null AND D.NoOfConnectionActivated Is Null AND D.NoOfConnectionCF Is Null AND D.NoOfConnectionReturned Is Null AND D.ActivationPercentage Is Null ) ; UPDATE I SET Commission = I.TotalFreeTextCommission, Description = @Free_Text FROM inserted As I INNER JOIN dbo.tblSelfBillFreeText As T ON T.SelfBillID = I.SelfBillID ; INSERT INTO dbo.tblSelfBillFreeText ( SelfBillID, Commission, Description ) SELECT SelfBillID, TotalFreeTextCommission, @Free_Text FROM inserted As I WHERE Not Exists ( SELECT 1 FROM dbo.tblSelfBillFreeText As T WHERE T.SelfBillID = I.SelfBillID ) ;ENDIn SQL Server triggers fire once per statement.you could try below based on your requirement.1) if possible at all modify the trigger to deal with all rows at once or2) import the data into a staging table, perform the required activity (set based, again) and move it to the final table. 这篇关于触发接受多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！