|
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? |
|
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 |
What have you tried doing so far? What problem did you have?
updata my qustion
@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).
@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
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.
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?