Thread: after trigger question

Hi,

I have put an AFTER INSERT  TRIGGER on a table.

If the triggerfunction fails to do an operation shouldn't the insert
already have taken place ?



--
Feite Brekeveld
feite.brekeveld@osiris-it.nl

On Thu, 22 Mar 2001, Feite Brekeveld wrote:

> Hi,
>
> I have put an AFTER INSERT  TRIGGER on a table.
>
> If the triggerfunction fails to do an operation shouldn't the insert
> already have taken place ?

It depends on what you mean by fails to do an operation.  If it
raises an actual error the transaction is going to be rolled back so the
insert is effectively undone.

Hi -
    I'm playing around with C functions, etc... and am running into a
strange problem where I cannot delete the operator once I've created it.
I can succesfully create/drop operators with two operands, just not this
one.

Here's a transcript.  What am I missing?  This is happening with 7.0.3.

Thanks!

template1=# \c test
You are now connected to database test.
test=# CREATE FUNCTION am_int_bit_not(int2) RETURNS int2
test-# AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so', 'am_int_bit_not'
test-# LANGUAGE 'C';
CREATE
test=# CREATE OPERATOR ~ (
test(#     RIGHTARG = INT2,
test(#     PROCEDURE = am_int_bit_not
test(#     );
CREATE
test=# \do ~
                          List of operators
 Op | Left arg | Right arg | Result |          Description
----+----------+-----------+--------+--------------------------------
 ~  |          | int2      | int2   |
 ~  | _aclitem | aclitem   | bool   | matches regex., case-sensitive
 ~  | bit      | bit       | bit    | bitwise negation
 ~  | box      | box       | bool   | contains
 ~  | bpchar   | text      | bool   | matches regex., case-sensitive
 ~  | circle   | circle    | bool   | contains
 ~  | circle   | point     | bool   | circle contains point?
 ~  | name     | text      | bool   | matches regex., case-sensitive
 ~  | path     | point     | bool   | path contains point?
 ~  | polygon  | point     | bool   | polygon contains point?
 ~  | polygon  | polygon   | bool   | contains
 ~  | text     | text      | bool   | matches regex., case-sensitive
 ~  | varbit   | varbit    | varbit | bitwise negation
 ~  | varchar  | text      | bool   | matches regex., case-sensitive
(14 rows)

test=# select ~ 2::int2;
 ?column?
----------
       -3
(1 row)

test=# DROP OPERATOR ~ (NONE, INT2);
ERROR:  RemoveOperator: left unary operator '~' taking 'int2' does not exist
test=# DROP OPERATOR ~ (INT2, NONE);
ERROR:  RemoveOperator: right unary operator '~' taking 'int2' does not exist

Philip Hallstrom <philip@adhesivemedia.com> writes:
> test=# DROP OPERATOR ~ (NONE, INT2);
> ERROR:  RemoveOperator: left unary operator '~' taking 'int2' does not exist

This is fixed in 7.1.  I think in 7.0 you'll have to delete the pg_operator
row manually.

            regards, tom lane

Stephan Szabo wrote:

> On Thu, 22 Mar 2001, Feite Brekeveld wrote:
>
> > Hi,
> >
> > I have put an AFTER INSERT  TRIGGER on a table.
> >
> > If the triggerfunction fails to do an operation shouldn't the insert
> > already have taken place ?
>
> It depends on what you mean by fails to do an operation.  If it
> raises an actual error the transaction is going to be rolled back so the
> insert is effectively undone.

The conversion of a timestring to an integer value fails because it is
sometimes illegal formatted , but I would like to have the the original
data inserted in the table as a string.

--
Feite Brekeveld
feite.brekeveld@osiris-it.nl

From: "Feite Brekeveld" <feite.brekeveld@osiris-it.nl>

> Stephan Szabo wrote:
>
> > On Thu, 22 Mar 2001, Feite Brekeveld wrote:
> >
> > > Hi,
> > >
> > > I have put an AFTER INSERT  TRIGGER on a table.
> > >
> > > If the triggerfunction fails to do an operation shouldn't the insert
> > > already have taken place ?
> >
> > It depends on what you mean by fails to do an operation.  If it
> > raises an actual error the transaction is going to be rolled back so the
> > insert is effectively undone.
>
> The conversion of a timestring to an integer value fails because it is
> sometimes illegal formatted , but I would like to have the the original
> data inserted in the table as a string.

Use a BEFORE INSERT trigger then to check/translate the timestring before
you insert.

- Richard Huxton