Geek asks and answers

How to create a trigger in postgresql to check function exit or not

I am new to postgresql and parts of the database. I want to check the function output or not in postgresql using a trigger before insertion. Does any body know how to create a trigger for this?

+3
source share
1 answer

Create a special launch function:

CREATE OR REPLACE FUNCTION some_function() 
RETURNS trigger AS $$
    BEGIN
        IF some_condition THEN
            RAISE EXCEPTION 'Some message for id %', NEW.id;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Then define a trigger to fire it.

CREATE TRIGGER some_trigger 
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW EXECUTE PROCEDURE some_function();

If an exception occurs, the update will not be performed.

Executed SQL can be either arbitrary complexity, including any query or function that you like. It can also change NEW values ​​through SET NEW.some_column = some_valueor even update other tables.

, NEW.id - . ( none) .

, , :

CREATE TABLE my_table (
    some_column int CHECK (some_column between 0 and 10),
    other_column int CHECK (other_column < some_column)
)
+4

More articles:


All Articles