Re: Triggers: Detecting if a column value is explicitly set in an UPD ATE statement or not... ("IS NULL" not working?)

On Wed, Aug 01, 2007 at 09:30:25AM -0500, Weber, Geoffrey M. wrote:
> I want to set a BOOLEAN column value to FALSE by default for all INSERT and
> UPDATE statements performed against a particular table _UNLESS_ it's
> explicitly set to TRUE in the SQL statement. Here is the trigger I created:

The insert case is easy, just set the default for the field to false.

For updates, you'll want a trigger that compares NEW.field to OLD.field,
and if they're the same sets NEW.field to false.

> CREATE TABLE table1 ( id INTEGER PRIMARY KEY, data VARCHAR(64), b_flag
> BOOLEAN);
>
> CREATE OR REPLACE FUNCTION func1() RETURNS trigger AS $func1$
> BEGIN
> RAISE NOTICE 'NEW.b_flag=%', NEW.b_flag;
> IF ( NEW.b_flag IS NULL ) THEN
> NEW.b_flag := FALSE;
> END IF;
>
> RETURN NEW;
> END;
> $func1$ LANGUAGE plpgsql;
>
> CREATE TRIGGER func1 BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW EXECUTE
> PROCEDURE func1();
>
>
>
> What I get, however, is that for an UPDATE, the "NEW.b_flag" value evaluates
> to TRUE if it has been set to TRUE by a previous UPDATE that explicitly sets
> it. So...
>
> tqa=> INSERT INTO table1 VALUES ( '1', 'some data');
> NOTICE: NEW.b_flag=<NULL>
> INSERT 0 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
> b_flag
> --------
> f
> (1 row)
>
> tqa=> UPDATE table1 SET b_flag=TRUE where id='1';
> NOTICE: NEW.b_flag=t
> UPDATE 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
> b_flag
> --------
> t
> (1 row)
>
> tqa=> UPDATE table1 SET data='new data' where id='1';
> NOTICE: NEW.b_flag=t
> UPDATE 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
> b_flag
> --------
> t
> (1 row)
>
>
>
> As you can see, I put a RAISE NOTICE and verified that for some reason, the
> NEW row contains a 'TRUE' value for b_flag, even though I didn't explicitly
> set it in the last UPDATE statement. Why does it seem to be reading the
> value from the OLD row for that column unless I override it inside the SQL
> statement? Is there any way to achieve the desired result without having to
> explicitly set 'b_flag' each time I touch a row in the table?
>
>
>
>
> NOTICE: This electronic mail transmission may contain confidential
> information and is intended only for the person(s) named. Any use, copying
> or disclosure by any other person is strictly prohibited. If you have
> received this transmission in error, please notify the sender via e-mail.
>
>
>

--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)