SQLTeam.com | Weblogs | Forums

Triggers, Stored Procedures and Integrity Constraints


#1

Hello guys,

I started studying SQL and I have found myself stuck in a set of exercises regarding triggers and that kind of approach. Taking that into account I would like some help/suggestion to the following business rules to be applied to my model.

Link with the data base model: https://drive.google.com/file/d/1xt50y75rwv4fGB-HqQWhQEBKwmfpndzL/view

Question 1 - Everytime a "Items Oferta" is loaded it should be loaded information into the table "Stocks" that allows at any time to know (and update) the available stock from a specific "Items Oferta".

Question 2 - When loading an "Item Reserva" it is necessary to have the guarantee that there is available "Stock" to fullfill that "Items Reserva"; if that does not happen the "Items Reserva" cannot be loaded.

Question 3 - The sender of a message is one of the entities envolved in the "Amizade" relationship associated with the "Message" and is identified with the ID of the entity.

Question 4 - It is necessary to guarantee that the "Amizade" relations exist only between "Utilizadores" or between "Utilizadores" and "Empresas". The relation "Amizade" between "Empresas" does not exists.

Question 5 - Is not mandatory that one "Meio de Pagamento" identifies one "Utilizador" (optional field). If is one "Meio de Pagamento" of an ad-hoc "Utilizador" (utilizador is NULL) then the E-Mail cannot be Null.

Can anyone help me in this business rules using triggers?

Regards.