Re: after insert or update or delete of col2

On Tue, Nov 01, 2005 at 12:33:47AM +0100, Rafael Montoya wrote:
> I have this statement in oracle:
>
> CREATE OR REPLACE TRIGGER trig
> AFTER INSERT OR UPDATE OR DELETE OF column2 <<----- Here is the doubt
> ON table_product
> FOR EACH ROW
> BEGIN
> ...
> END
>
> Migrating to PostgreSQL, the conditionals for AFTER UPDATE OF COLUMN2 in
> trig() are:
>
> IF NEW.column2 <> OLD.column2 OR
> (NEW.column2 IS NULL) <> (OLD.column2 IS NULL) THEN
> ...
> END IF;

A simpler condition would be

IF NEW.column2 IS DISTINCT FROM OLD.column2 THEN
...
END IF;

IS DISTINCT FROM is like <> except that it works with NULL:

NULL IS DISTINCT FROM NULL -- false
NULL IS DISTINCT FROM something -- true

If you're using the same function for insert, update, and delete
triggers then you'll need to check TG_OP before executing the above
code; otherwise you'll get an error like 'record "old" is not
assigned yet'.

IF TG_OP = 'UPDATE' THEN
IF NEW.column2 IS DISTINCT FROM OLD.column2 THEN
...
END IF;
END IF;

The nested IF is necessary because you can't depend on short-circuiting
as in some other languages.

> but, i can not found the conditionals for AFTER INSERT OF COL2 and AFTER
> DELETE OF COL2, please, give me a hand.

Does a column list affect trigger behavior for inserts and deletes?
I don't see those behaviors defined in SQL:2003:

<trigger event> ::=
INSERT
| DELETE
| UPDATE [ OF <trigger column list> ]

What, if anything, is different between "AFTER INSERT OF COL2" and
a simple "AFTER INSERT"?

--
Michael Fuhr