Re: Enforcing minimum on many-to-many relationship?

On Thu, Nov 29, 2012 at 1:03 PM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
> I want to be able to enforce the condition that there will always be at
> least one row in table 'x' for each row in 'a'. I.e, a row in 'a' must
> always be related to at least one 'r'.
>
> My first thought was a trigger at delete time. That works, BUT, the trigger
> also apparently fires on a FK cascade, preventing deleting a row in 'a'
> since the cascade will attempt to delete all the rows in 'x'.
>
> Is it possible to disable or otherwise bypass the trigger on cascade without
> affecting other transactions?

From the documentation: "When a row-level AFTER trigger is fired, all
data changes made by the outer command are already complete, and are
visible to the invoked trigger function".

So you need to make your trigger AFTER.

>
> The application is difficult to change, so I'd like to do this without
> requiring it to call stored procedures if possible.
>
> Thoughts?
>
>
> Mike
>
>

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com