在Postgres中创建一个触发器函数，该函数不允许您有两个具有相同ID的条目 [英] Create a trigger function in Postgres that doesn't let you have two entries with the same id

问题描述

我在postgres中有两个表，我想创建一个函数，该函数在借贷表中拥有相同个人ID的贷款不超过2笔.例如:在贷款表中，我不能拥有来自同一个人的3笔贷款，也就是说，我们以同一个人的ID贷款.

I have two tables in postgres, I want to create a function that doesn’t have more than 2 loans in the lending table with the same person id. example: in the loan table I cannot have 3 loans that are from the same person, that is, we loan with the same person's id.

我需要使用一个函数来完成此操作，我放了我想做的事，但是没有用

I need to do this using a function, I put what I was trying to do but it didn't work

CREATE TABLE person (
    name_person varchar (100) ,
    id_person varchar(14) primary key
)

CREATE TABLE lending(
    id_lending primary key (100) ,
    id_publication (14) FK,
    id_person fk REFERENCES id_person (person)




CREATE OR REPLACE FUNCTION check_numlending() 
RETURNS trigger AS 
$BODY$ 
BEGIN 
IF( select * from lending
   inner join person 
   on person.id_person = lending.id_person > 2  ) THEN 
    RAISE EXCEPTION 'ERROR'; 
END IF; 
RETURN NEW; 
END; 
$BODY$ 
LANGUAGE plpgsql; 


-- Trigger
CREATE TRIGGER  
trg_check_num_lending  
BEFORE INSERT OR UPDATE ON 
lendingFOR EACH ROW EXECUTE PROCEDURE check_numlending(); 

推荐答案

编写触发函数，如下所示:

Write your trigger Function like below:

-- Function
CREATE OR REPLACE FUNCTION check_numlending() 
RETURNS trigger AS 
$BODY$ 
declare counter int;
BEGIN 
select count(*) into counter from lending where id_person =new.id_person;
IF( counter>=2  ) THEN 
    RAISE EXCEPTION 'ERROR'; 
END IF; 
RETURN NEW; 
END; 
$BODY$ 
LANGUAGE plpgsql; 


-- Trigger
CREATE TRIGGER  
trg_check_num_lending  
BEFORE INSERT OR UPDATE ON 
lending FOR EACH ROW EXECUTE PROCEDURE check_numlending(); 

这篇关于在Postgres中创建一个触发器函数，该函数不允许您有两个具有相同ID的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！