Trigger causing an error... 2

[webmigit]

I have a trigger that is causing an error when multiple items are inserted to the db (I use cold fusion, each query is in a seperate <cfquery> tag.)

CREATE TRIGGER InsertBalance ON [dbo].[Credits] 
FOR INSERT, UPDATE
AS
DECLARE @CreditUserID INT

[red]SELECT @CreditUserID = (SELECT CreditUserID FROM Inserted)[/red]

update users
    set balance = Abs(ISNULL((select sum(creditamount)
        from credits
        where creditUserID = @CreditUserID),0))
    where userid = @CreditUserID

When one insert query is run, it runs just fine, but when multiple are passed, I get an error

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The error occurred on line 41.

I'm assuming that the red line in the code section above is what's causing the error... How do I avoid this? How do I process after each and every insert query rather than in bulk?

 

[SQLDenis]

multi row statements


untested

CREATE TRIGGER InsertBalance ON [dbo].[Credits] 
FOR INSERT, UPDATE
AS
DECLARE @CreditUserID INT

SELECT @CreditUserID = (SELECT CreditUserID FROM Inserted)

update u
    set balance = Abs(ISNULL((select sum(creditamount)
        from credits c 
	join users u on c.userid = u.userid
	join Inserted i on c.creditUserID = i.CreditUserID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

take out the SELECT @CreditUserID = (SELECT CreditUserID FROM Inserted) part

CREATE TRIGGER InsertBalance ON [dbo].[Credits] 
FOR INSERT, UPDATE
AS

update u
    set balance = Abs(ISNULL((select sum(creditamount)
        from credits c 
    join users u on c.userid = u.userid
    join Inserted i on c.creditUserID = i.CreditUserID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

How many CreditUserId you have in Inserted? I suppose it is only one. That you can change this trigger to:

CREATE TRIGGER InsertBalance ON [dbo].[Credits]
FOR INSERT, UPDATE
AS
DECLARE @CreditUserID INT

SELECT @CreditUserID = (SELECT DISTINCT CreditUserID
                                        FROM Inserted)
--- OR
--- SELECT @CreditUserID = (SELECT TOP 1 CreditUserID
---                                FROM Inserted
---                                ORDER BY CreditUserID)

update users
    set balance = Abs(ISNULL((select sum(creditamount)
        from credits
        where creditUserID = @CreditUserID),0))
    where userid = @CreditUserID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

bborissov,

triggers fire per statement not per row

Unless you are 100% sure that there can never ever be a multirow insert or update assignment of variables should not be done

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Agree,
But in that case webmigit uses ID. That's why I supposed there will be no more that one DISTINCT record for that ID.
BTW I like you anser more. It fits every occasions.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[webmigit]

Wow guys. Thanks!

I have one more question... How would I add simple error catching to this? I can't afford for it to have errors.

Can I have it email me, in any way to tell me when the sql fails?

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[bborissov]

SQL Denis's code with some error handling:

CREATE TRIGGER InsertBalance ON [dbo].[Credits]
FOR INSERT, UPDATE
AS

DECLARE @nError
update u
    set balance = Abs(ISNULL((select sum(creditamount)
        from credits c
    join users u on c.userid = u.userid
    join Inserted i on c.creditUserID = i.CreditUserID
@nError = @@ERROR
IF @nError <> 0
   BEGIN
      IF @@TRANCOUNT >  0
         ROLLBACK -- Roll back the transaction if any

      EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'Your ProfileName',
           @recipients = 'YourEMail@MailHost.com',
           @body = 'The trigger failed with error #'+CAST(@nError as varchar(10)),
           @subject = 'Trigger Failed' ;
    END

Check sp_send_dbmail in BOL for more help.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[webmigit]

Alright...

Thanks for the answers so far.. One more question regarding error handling.

@nError = @@ERROR
IF @nError <> 0
   BEGIN
[red]      IF @@TRANCOUNT >  0
         ROLLBACK -- Roll back the transaction if any[/red]

      EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'Your ProfileName',
           @recipients = 'YourEMail@MailHost.com',
           @body = 'The trigger failed with error #'+CAST(@nError as varchar(10)),
           @subject = 'Trigger Failed' ;
    END

will handle all errors that the trigger causes right? So long as the trigger is syntactically well formed? So that if the trigger query causes an error, it shouldn't affect the user experience?

As for the lines marked in red, they roll back the transaction.. but do they rol back the trigger transaction or the trigger transaction AND the original transaction that fired the trigger?

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

This is the finished product, apparently working, but 'd like some opinion

update users
    set balance = (ISNULL((select sum(creditamount)
        from credits
        where creditUserID = users.UserID
            and left(CreditDate,11) = left(GetDate(),11)),0))
    where userid in (Select credituserid from inserted)

This is a revised version with the error report... not tested...

...create trigger...

declare @nError

update users
    set balance = (ISNULL((select sum(creditamount)
        from credits
        where creditUserID = users.UserID
            and left(CreditDate,11) = left(GetDate(),11)),0))
    where userid in (Select credituserid from inserted)

@nError = @@ERROR
IF @nError <> 0
   BEGIN
      IF @@TRANCOUNT >  0
         ROLLBACK -- Roll back the transaction if any

      EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'Your ProfileName',
           @recipients = 'YourEMail@MailHost.com',
           @body = 'The trigger failed with error #'+CAST(@nError as varchar(10)),
           @subject = 'Trigger Failed' ;
    END

Is the value of @nError short enough to fit in varchar(10) or should I cast as... ntext to make sure I get all of it?