Trigger to judge whether the input data from A total is greater than the B table

[ximen]

A table: TRANS_No part_id quantity unit_price ar_amt customer_id

         TR001       A001    10        30          300      Customer001

         TR001       A004    20        40          800      customer001

  B tabel:

          customer_id     limit

          customer001       900
insert A if
 sum(a.ar_amt)>b.limit      --Are not allowed to insert data
 sum(a.ar_amt)<b.limit      --can insertdata)

How to use the trigger judge whether the insert data of A total is greater than the table B, if the limit is greater than B is not allowed to insert So what do I need the triggers. If he is greater than the number that I will let him save, the judgment of statements or constraint statement I should how to write?

[justin_willey]

What have you tried doing so far? What problem did you have?

[ximen]

updata my qustion

[VolkerBarth]

@mfkpie8: Please show us the trigger code you have tried to write, so we can make suggestions.

Basically you will use the ROLLBACK TRIGGER statement within the trigger to prevent an undesired insert.

You could as well use a CHECK CONSTRAINT within table A to prevent such a modification (though it would prevent updates on rows that would break the limit, too).

[ximen]

@volker barth Constraint is a table, but I want to insert can control whether will be greater than this value! Because it is two tables. Constraints do is check, but I this is a hint of rolled back

[VolkerBarth]

Yes, I'm aware that there are two tables in use. However, you can surely use a CHECK constraint to check a value against values of a different table (by using a subquery). But I think here a trigger-based approach is more straight-forward - just show us what you have tried so far.

[ximen]

HI voller: So what do I need the triggers. If he is greater than the number that I will let him save, the judgment of statements or constraint statement I should how to write?

[ximen · Accepted solution]

Thanks for jason.hinsperger help me : The answer is:

 Declare tot integer;

    Declare lim integer;

    Select sum(amt) into tot where customer_id = newrow.customer_id;

    Select limit into lim where customer_id = newrow.customer_id;

    If tot > lim then

                    Raiserror 30002 ‘You cannot go over your limit’;

    endif