Conditional insert in SQL

Question

I was looking for an example of data consistency over tables by using ACID transaction. I saw the example here : http://microservices.io/patterns/data/shared-database.html summery is as follow: CUSTOMER table has a column CREDIT_LIMIT. we want to insert into ORDER table a new record If OrderPrice< CREDIT_LIMIT of that user. Written query in the example is as follow and I believe it is not complete:

BEGIN TRANSACTION
 SELECT ORDER_TOTAL
  FROM ORDERS WHERE CUSTOMER_ID = 123
SELECT CREDIT_LIMIT
  FROM CUSTOMERS WHERE CUSTOMER_ID = 123
 INSERT INTO ORDERS
COMMIT TRANSACTION

According to the tutorial, to keep data consistency over 2 tables, there should be an "If check" in query. Anyone can complete the query ? I would appreciate.

score 1 · Answer 1 · answered Dec 06 '16 at 17:17

1

I would implement this differently:

IF ((SELECT (A.CREDIT_LIMIT - B.ORDER_TOTAL) 
       FROM CUSTOMERS A , ORDERS B 
      WHERE A.CUSTOMER_ID = 123
        AND B.CUSTOMER_ID = 123)
    ) > 0
   ) THEN
     INSERT...
END IF ;

answered Dec 06 '16 at 17:17

FDavidov

3,505
6
23
59

Thanks a lot. How do you implement it in context of transactions? can you complete the query in question ? – Danial Dec 06 '16 at 17:52
The use of **transactions** needs to be analyzed with extreme care. Could you explain why do you need it? – FDavidov Dec 07 '16 at 16:34
To learn how transactions guaranty data accuracy between different tables. – Danial Dec 08 '16 at 11:10
I see... I suggest you focus your efforts on documentation since setting a scenario in which this features take over is not going to be an easy task. Enjoy your reading!!! – FDavidov Dec 08 '16 at 11:13

Conditional insert in SQL

1 Answers1