Trigger to accept mulitple value

Question

0.00/5 (No votes)

See more:

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]

SQL

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)

Posted 8-May-15 0:40am

Member 11669307

Updated 8-May-15 1:28am

CHill60

v2

Add a Solution

Comments

CHill60 8-May-15 6:45am

Share the code that is in your trigger to do the update

Member 11669307 8-May-15 7:05am

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)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-05-08T01:37:00

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

Richard Deeming · Answer 2 · 2015-05-08T02:14:00

This should do the trick:

SQL

ALTER TRIGGER [dbo].[selfbill] 
ON [dbo].[tblSelfBill]
FOR UPDATE
As
BEGIN
DECLARE @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
        )
    ;
END

Amaan23 · Answer 3 · 2015-05-08T00:55:00

In 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 or
2) import the data into a staging table, perform the required activity (set based, again) and move it to the final table.

Trigger to accept mulitple value

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0