[pgbr-geral] ajuda com disparador

[Osvaldo Kussama]

O teste está errado. o campo não informado não contém NULL. Teste se é
igual ao OLD.

Veja:
bdteste=# CREATE TEMP TABLE teste (id serial primary key, x int, x100 int);
NOTICE:  CREATE TABLE will create implicit sequence "teste_id_seq" for
serial column "teste.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"teste_pkey" for table "teste"
CREATE TABLE
bdteste=#
bdteste=# CREATE OR REPLACE FUNCTION funcao_teste() RETURNS trigger AS
bdteste-# $$
bdteste$# BEGIN
bdteste$# RAISE NOTICE '% - id: % - x: % - x100: %', TG_OP, NEW.id,
NEW.x, NEW.x100;
bdteste$# IF (TG_OP = 'INSERT') THEN
bdteste$#        IF (NEW.x IS NOT NULL) THEN
bdteste$#                NEW.x100 = NEW.x*100;
bdteste$#        ELSE
bdteste$#                NEW.x = NEW.x100/100;
bdteste$#        END IF;
bdteste$# ELSE
bdteste$#        IF (TG_OP = 'UPDATE') THEN
bdteste$#                IF (NEW.x <> OLD.x) THEN
bdteste$#                        NEW.x100 = NEW.x*100;
bdteste$#                ELSE
bdteste$#                        IF (NEW.x100 <> OLD.x100) THEN
bdteste$#                                NEW.x = NEW.x100/100;
bdteste$#                        END IF;
bdteste$#                END IF;
bdteste$#        END IF;
bdteste$# END IF;
bdteste$# RETURN NEW;
bdteste$# END;
bdteste$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
bdteste=#
bdteste=# CREATE TRIGGER funcao_teste BEFORE INSERT OR UPDATE ON teste
FOR EACH ROW EXECUTE PROCEDURE funcao_teste();
CREATE TRIGGER
bdteste=#
bdteste=# INSERT INTO teste(x) VALUES(1);
NOTICE:  INSERT - id: 1 - x: 1 - x100: <NULL>
INSERT 0 1
bdteste=# INSERT INTO teste(x100) VALUES (200);
NOTICE:  INSERT - id: 2 - x: <NULL> - x100: 200
INSERT 0 1
bdteste=# SELECT * FROM teste;
 id | x | x100
----+---+------
  1 | 1 |  100
  2 | 2 |  200
(2 rows)

bdteste=#
bdteste=# UPDATE teste SET x = 3 WHERE x = 2;
NOTICE:  UPDATE - id: 2 - x: 3 - x100: 200
UPDATE 1
bdteste=# SELECT * FROM teste;
 id | x | x100
----+---+------
  1 | 1 |  100
  2 | 3 |  300
(2 rows)

bdteste=#
bdteste=# UPDATE teste SET x100 = 200 WHERE x = 3;
NOTICE:  UPDATE - id: 2 - x: 3 - x100: 200
UPDATE 1
bdteste=# SELECT * FROM teste;
 id | x | x100
----+---+------
  1 | 1 |  100
  2 | 2 |  200
(2 rows)

Osvaldo