Recursive triggers and error : subquery returned more than 1 value

Question

I need your help !

I am working on sql server 1-- I created this trigger but it seems to be wrong...

CREATE TRIGGER [dbo].[chargeAZero] 
ON [dbo].[situations_final] 
after INSERT 
AS 
  BEGIN 
      SET nocount ON 

      UPDATE sfinal 
      SET    charge = 00 
      FROM   inserted i 
             INNER JOIN situations_final sfinal 
                     ON i.referencepiece = sfinal.referencepiece 
                        AND i.ancienposte = sfinal.ancienposte 
                        AND i.numerophase = sfinal.numerophase 
                        AND i.datestrategie = sfinal.datestrategie 
      /*and i.Datecadence=sfinal.Datecadence*/ 
      WHERE  (SELECT sfinal.nouveauposte 
              FROM   situations_final sfinal 
                     INNER JOIN inserted i 
                             ON i.referencepiece = sfinal.referencepiece 
                                AND i.ancienposte = sfinal.ancienposte 
                                AND i.numerophase = sfinal.numerophase 
                                AND i.datestrategie = sfinal.datestrategie) IS 
             NULL 
  END 

The error message is always the same: the subquery returned more than one value... I think I wrote my trigger correctly as I did with others that work fine.

2-- My second question is : Is it possible to make only one trigger recursive ?

3-- As you have noticed on my database on the table "Nomenclatures" (Bill of materials in english) I have 3 elements: *codepiecemere: The component mother *codepiecefille: the component child * the quantity. I give you an example of what I need : Mother= A Child= B Quantity= 2 Mother= B Child= C Quantity= 3

I want a trigger to give me a result like that: A 1 B 2 C 6=2*3 (the quantity needed of C to make 1 B).

Thank you very much

Answer 1

I am ambitious :D I tried to improve the script:

WITH RESULT (MOTHER, CHILD, QUANTITY)
        as
        (
            select Mother, Child, CONVERT(Numeric(10,0), Quantity) as Quantity from bilangammestest 
            union all select M.mother, R.Child, CONVERT(Numeric(10,0), M.quantity * R.Quantity) as Quantity from Result R 
            INNER JOIN bilangammestest M ON M.Child = R.Mother

            )
            select * from result
            where mother not in (select child from bilangammestest )

Here are the data I have on my table "Bilangammestest":

Z A 1

Z Y 1

A B 2

Y B 2

B C 3

Here are the result I get :

Z A 1

Z Y 1

Z C 6

Z C 6

Z B 2

Z B 2

Here is the Final result I want:

Z A 1

Z Y 1

Z C 12

Z B 4

I tried to do a 'sum' but I couldn't do it correctly :(

Answer 2

Here's a recursive query that solves the material aggregation problem.

Table definition

CREATE TABLE [dbo].[Material](
  [Mother] [varchar](100) NOT NULL,
  [Child] [varchar](100) NOT NULL,
  [Quantity] [int] NOT NULL,
)

and the query:

WITH Result(mother, child, quantity)
AS
(
    select * from material 
    union all
    select M.mother, R.Child,  M.quantity * R.Quantity as Quantity
    from Result R INNER JOIN Material M ON M.Child = R.Mother 
 )
 select * from result

You can see an example here: http://sqlfiddle.com/#!6/6dc64/1

UPDATE:

Sql fiddle is not working, I don't know why

UPDATE 2

Sql Fiddle is back! :-)

Answer 3

I think the problem is that you are inserting more than one row in a single command, so the inserted table contains more than one row. As a consequence the sub query

SELECT sfinal.nouveauposte 
          FROM   situations_final sfinal 
                 INNER JOIN inserted i 
                         ON i.referencepiece = sfinal.referencepiece 
                            AND i.ancienposte = sfinal.ancienposte 
                            AND i.numerophase = sfinal.numerophase 
                            AND i.datestrategie = sfinal.datestrategie

contains more than one row too and cannot be compared to NULL that is a scalar value.

Answer 4

The is null is not normally used with subqueries. Try this:

        where  not exists (select 1
                           from SITUATIONS_Final sfinal inner join inserted i
                                on i.ReferencePiece=sfinal.ReferencePiece
                                   and i.AncienPoste=sfinal.AncienPoste
                                   and i.numerophase=sfinal.numerophase
                                   and i.datestrategie=sfinal.datestrategie
                          )

This is assuming that the is null is testing for no values being returned, as opposed to a NULL value in sfinal.nouveauposte. If the latter:

        where exists (select 1
                      from SITUATIONS_Final sfinal inner join inserted i
                           on i.ReferencePiece=sfinal.ReferencePiece
                              and i.AncienPoste=sfinal.AncienPoste
                              and i.numerophase=sfinal.numerophase
                              and i.datestrategie=sfinal.datestrategie
                      where sfinal.nouveauposte is null
                     )

EDIT:

Do you need the subquery at all?

  UPDATE sfinal 
  SET    charge = 00 
  FROM   inserted i 
         INNER JOIN situations_final sfinal 
                 ON i.referencepiece = sfinal.referencepiece 
                    AND i.ancienposte = sfinal.ancienposte 
                    AND i.numerophase = sfinal.numerophase 
                    AND i.datestrategie = sfinal.datestrategie 
  WHERE sfinal.nouveauposte IS NULL;