Re: Update function

"Sharon Cowling" <sharon.cowling@sslnz.com> writes:
> I'm having trouble with a function

(a) returning NULL from a before-trigger is a signal to the system to
skip the insert (or update or delete).  You need "return new" instead,
at least in the case where you want the insert to happen.

(b) this is pretty bizarre:

>     IF new.my_code LIKE (SELECT my_code FROM permit WHERE my_code = new.my_code) THEN

Perhaps you meant

    IF EXISTS(SELECT 1 FROM permit WHERE my_code = new.my_code) THEN

(c) this will have a syntax error whenever you finally reach it:

>     UPDATE permit SET my_code LIKE ''R'' WHERE old.my_code LIKE new.my_code;

In general you seem to be much too eager to use LIKE where = would do.
= is a lot cheaper, and isn't going to surprise you with odd behavior
on strings containing % or _ ...

            regards, tom lane