Breaking a nested trigger

Hi,
I have the following 2 tables:
tbl_CustomerEmail1
(
CustomerID1 int,
EmailAddress varchar(200),
Unsubscribe bit
)
tbl_CustomerEmail2
(
CustomerID2 int,
EmailAddress varchar(200),
Unsubscribe bit
)
On each table I have a trigger to subscribe/unsubscribe a customer in the
other table if it exists:
CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
begin
update tbl_Customer1
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
and:
CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
begin
update tbl_Customer2
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
These 2 triggers fire each other to the nested limit (32) if I try to
subscribe/unsubscribe a customer which exists in both tables yet I can't
disallow the nested trigger property on the server because it may break othe
r
applications in other DB's. The return statement used doesn't work. Is there
a way I can do this without disabling the nested trigger property on the
whole server?
Many thanks for your help.Elisabeth,
If the updates that fire these triggers are never
called from yet other triggers, you should be able
to handle this by putting the following line of code
at the very beginning of each trigger:
if trigger_nestlevel() > 2 return
A better solution is probably to redesign these tables
so that there is no need to store the same information
in two separate places, but for now, using the system
function trigger_nestlevel() may take care of things.
Steve Kass
Drew University
Elisabeth wrote:

>Hi,
>I have the following 2 tables:
>tbl_CustomerEmail1
>(
>CustomerID1 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>tbl_CustomerEmail2
>(
>CustomerID2 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>On each table I have a trigger to subscribe/unsubscribe a customer in the
>other table if it exists:
>
>CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
> begin
> update tbl_Customer1
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>and:
>
>CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
> begin
> update tbl_Customer2
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>
>These 2 triggers fire each other to the nested limit (32) if I try to
>subscribe/unsubscribe a customer which exists in both tables yet I can't
>disallow the nested trigger property on the server because it may break oth
er
>applications in other DB's. The return statement used doesn't work. Is ther
e
>a way I can do this without disabling the nested trigger property on the
>whole server?
>Many thanks for your help.
>
>
>|||worked a treat! cheers mate.
"Steve Kass" wrote:

> Elisabeth,
> If the updates that fire these triggers are never
> called from yet other triggers, you should be able
> to handle this by putting the following line of code
> at the very beginning of each trigger:
> if trigger_nestlevel() > 2 return
> A better solution is probably to redesign these tables
> so that there is no need to store the same information
> in two separate places, but for now, using the system
> function trigger_nestlevel() may take care of things.
> Steve Kass
> Drew University
>
> Elisabeth wrote:
>
>