From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Rafael Montoya <rafo-mm(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: after insert or update or delete of col2 |
Date: | 2005-11-01 08:24:22 |
Message-ID: | 20051101082422.GA53178@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew D. Fuller | 2005-11-01 10:45:13 | Re: SQL injection |
Previous Message | Teguh R | 2005-11-01 08:20:26 | Re: trapping errors in plpgsql? |