PostgresSQL trigger creation

Question

Guys, I can not cope with this task: I have a database of the service center, and I need to create a trigger - When adding services to the order, check that the part (spare parts) is in stock. I would be happy to help. . That is, when adding a new “repair order” (idservice fields (service code)), we need to give an error message that the spare parts are not available. If available, the data in the repairorder table (repair order) must be filled. What so far implemented:

create function funcTrigCheck() RETURNS trigger AS ' BEGIN IF (select count(spares.quantity)<1 from spares, typesofservices, listofworks, repairorder where repairorder.idservice=listofworks.idservice and listofworks.idservice=typesofservices.idservice and typesofservices.idspares=spares.idspares) THEN RAISE EXCEPTION ''Запчастей нет!''; END IF; return null; End; ' language plpgsql; create trigger t4 before INSERT on repairorder for each row execute procedure funcTrigCheck();

How can I organize the inspection of some specific parts, what is the error in my function? That is, this function does not work correctly, count counts the total number of spare parts and displays a message.

Table "Order for repair":

 create table repairorder ( idorder integer NOT NULL primary key unique, idclients integer NOT NULL REFERENCES clients(idclients), idequipment integer NOT NULL REFERENCES equipment(idequipment), idstatus integer NOT NULL REFERENCES orderstatus(idstatus), idservice integer NOT NULL REFERENCES typesofservices(idservice), sn varchar(100), disrepair varchar(100), equipment varchar(100));

Table "Types of services":

 create table typesofservices ( idservice integer NOT NULL primary key unique, idspares integer NOT NULL REFERENCES spares(idspares), description varchar(100), price integer );

Spare parts table:

 create table spares ( idspares integer NOT NULL primary key unique, name varchar(100), quantity integer );

The principle is ... Try to write SELECT (spares.idspares, SELECT COUNT ('your request')) FROM ... And do not forget in the WHERE subquery to add another condition that is associated with id

Accepted Answer · 2016-12-15T04:44:05

First, there is a more convenient way to specify the procedure body than with a string. Secondly, implicit joins are the ugly legacy of the SQL89 standard. Thirdly, it was worth it either to lay out the sql-code of the tables or, at least, in the scheme to indicate the real names of the tables and the real names of the fields. In addition, the relationship between the service table and the spare parts table looks like m2m, you will not get by with one table. And an example of the embed code to which you want to hang the trigger would not hurt. On the merits of the question, I can assume this solution:

 create or replace function check_spares() returns trigger as $$ begin if exists ( select 1 from spares as s inner join typesofservices as ts using(idspares) inner join listofworks as lw using(idservice) where lw.idservice = NEW.idservice and s.quantity > 0 ) then return NEW; else raise exception 'Запчастей нет!'; end if; end; $$ language plpgsql;

I tried to ensure that when adding the repairorder table to the idservice field, an error message would be displayed.
It only remains for me to figure out when an exception is thrown at your request.

Akina Akina 15.2k 1 golden mark 10 silver marks 30 bronze marks · Answer 2 · 2016-12-15T04:59:50

You have an ideological cant. Order formation is in no way connected with the availability of necessary spare parts for it - even if they are not there, the order must still be formed. And if there is a refusal - the client will go to another service - then this is an external solution that is not controlled at the database level.

This is a concern of the client part, not the base, to check and issue a message. Accordingly, it is the client part that should insert a record of the order with one request, and the second - check the availability of spare parts and materials, and in their absence give a message. And, possibly, in the reverse order - for the result of the check may influence the decision to create or not to create an order.

You have not only been given a task, but you have also indicated the way to solve it, and without any alternative?
I would work on a real task for the client - I would do what you recommend.

PostgresSQL trigger creation

2 answers 2

More articles: