CODEWITHSUNDEEP
postgresqltriggers
Soleyne
Soleyne

Reputation: 77

How to create with condition and select?

I want to do something like this :

Someone creates a customer and if the ct already exist so we check if the cn is the same and if it's not the same we raise an error but it' doesn't work and take a lot of time.

CREATE OR REPLACE FUNCTION existingCT()

RETURNS trigger AS $$ BEGIN

    IF ((SELECT COUNT(*) FROM customer WHERE ct= NEW.ct)!= 0) THEN 

        IF( (SELECT COUNT(*) FROM customer WHERE ct= NEW.ct) != (SELECT count(*) FROM customer WHERE ct= NEW.ct AND cn= NEW.cn)) THEN

            RAISE EXCEPTION 'This ct already exist for a cn';

        END IF;

             END IF;

    RETURN NEW;

 END;

 $$ LANGUAGE plpgsql ;

Upvotes: 1

Views: 55

Answers (1)

Andomar
Andomar

Reputation: 238246

You can look for a customer with the same ct and a different cn in one query:

IF EXISTS (SELECT * FROM customer WHERE ct = NEW.ct AND cn <> NEW.cn) THEN

A thrown exception should abort the INSERT for any type of trigger. But given that it's a check, I'd create the trigger as BEFORE INSERT.

Upvotes: 1

Related Questions