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?

asked 18 Nov '13, 00:36

mfkpie8's gravatar image

mfkpie8
86424851
accept rate: 10%

edited 19 Nov '13, 11:39

1

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

(18 Nov '13, 04:40) Justin Willey

updata my qustion

(18 Nov '13, 06:18) mfkpie8
Replies hidden

@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).

(18 Nov '13, 06:51) Volker Barth

@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

(18 Nov '13, 07:06) mfkpie8
1

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.

(18 Nov '13, 08:35) Volker Barth

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?

(19 Nov '13, 09:08) mfkpie8
showing 2 of 6 show all flat view

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
permanent link

answered 21 Nov '13, 10:44

mfkpie8's gravatar image

mfkpie8
86424851
accept rate: 10%

edited 21 Nov '13, 10:45

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×60

question asked: 18 Nov '13, 00:36

question was seen: 934 times

last updated: 21 Nov '13, 10:45