Check attribute in trigger function (PostgreSQL)

I have a task in school which requires me to create a table and a trigger. I don’t really know how to explain it but how can I check if cID is inside the select statement within the trigger function ? Basically my goal is to only allow cID values which are not inside of “SELECT * from Example2 natural join Example3”. Can anyone help me with that? Thank you.

CREATE TABLE Example(
cID INTEGER REFERENCES Example2(attr),
level INTEGER CHECK (level BETWEEN 1 AND 10)); 


CREATE FUNCTION exp() RETURNS TRIGGER AS
$$
BEGIN
 IF EXISTS (select * from Example2 natural join Example3) THEN
 RAISE EXCEPTION '...';
 END IF;
 return null;
 END; $$ language plpgsql;


CREATE CONSTRAINT TRIGGER trg
AFTER INSERT OR UPDATE ON Example
FOR EACH ROW
EXECUTE PROCEDURE exp();

​x
 
CREATE TABLE Example(cID INTEGER REFERENCES Example2(attr),level INTEGER CHECK (level BETWEEN 1 AND 10)); ​​CREATE FUNCTION exp() RETURNS TRIGGER AS$$BEGIN IF EXISTS (select * from Example2 natural join Example3) THEN RAISE EXCEPTION '...'; END IF; return null; END; $$ language plpgsql;​​CREATE CONSTRAINT TRIGGER trgAFTER INSERT OR UPDATE ON ExampleFOR EACH ROWEXECUTE PROCEDURE exp();​

Answer

   

     CREATE FUNCTION exp() RETURNS TRIGGER AS
        $$
        BEGIN
         IF EXISTS (select 1 from Example2  a where   a.cID  = new.cID  ) THEN
         RAISE EXCEPTION '...';
         END IF;
         return         RETURN NEW;
         END; $$ language plpgsql;

Advertisement

Answer