Re: Trigger - will not perform INSERT

On Dec 11, 2007, at 21:35, smiley2211 wrote:

>
> ex:
>
> CREATE TRIGGER mytrig AFTER INSERT OR UPDATE ON foo1 FOR EACH ROW
> EXECUTE
> PROCEDURE updatefoo1('datarow');
>
> The command itself is able to be executed without error. However,
> what then
> happens, is that whenever the application attempts to insert a
> record into
> foo1, it simply doesn't insert. Once I take the trigger off, it beings
> inserting again.
>
> I have checked permissions but INSERT only FAILS while trigger is
> enabled..
>
> Thanks...Michelle

This would be expected behaviour if the trigger were a BEFORE INSERT
one (instead of AFTER) and the procedure returned NULL. You might
want to check that is really not the case.

AFTER INSERT triggers don't fire until the row is actually inserted
into the table, so the only possibility I can see for the behaviour
you describe is that the stored procedure removes the record that was
just inserted.
Maybe there are statements in that procedure that attempt to remove
possible duplicates that also happen to match on the new record?

Regards,
--
Alban Hertroys

                "If you throw your hands up in the air,
                how're you gonna catch them?"




!DSPAM:737,475fc5969651302216542!