Nested Trigger Not Firing... 2

[skuhlman]

Table A has an AFTER DELETE trigger which performs an update to Table B. Table B has an AFTER INSERT,UPDATE,DELETE trigger which inserts a record to a log file. When I execute an update statement to Table B in Query Analyzer, its trigger functions properly. When I delete a record in Table A, the trigger on A fires and its update of Table B works, but the Table B trigger does not fire.

I did a sp_configure 'nested_triggers' which returned the values:
minimum - 0
maximum - 1
config_value 0
run_value - 0

I figured that the NESTING was set off so I did a sp_configure 'nested_triggers', 1 and then checked the values which were:
minimum - 0
maximum - 1
config_value 1
run_value - 0

I thought that would take care of my problem, but the 2nd trigger is still not firing. My SQL Server 7.0 documentation says that for changes to the nested_triggers properties, you do NOT need to restart the server.

Any suggestions?

 

[PHH1955]

Have you tried COMMIT after you update from the Table A trigger?

Post the code if you can?

 

[skuhlman]

create table dbo.TESTLOG (field1 int null, field2 char(1) null, field3 datetime null)

go

Create Table dbo.TESTA (field1 int null, field2 char(1) null)
insert into testa values (1, 'A')
insert into testa values (2, 'B')
insert into testa values (3, 'C')
insert into testa values (4, 'D')

go

CREATE TRIGGER tr_AFTER_DELETE_TableA ON [dbo].[testa]
AFTER DELETE
AS
print 'Running Table A After Delete Trigger'
update testB
set field1 = testb.field1+10
from testb join deleted on testb.field2 = deleted.field2
commit

go

Create Table dbo.TESTB (field1 int null, field2 char(1) null)
insert into testb values (1, 'A')
insert into testb values (2, 'B')
insert into testb values (3, 'C')
insert into testb values (4, 'D')

go

CREATE TRIGGER tr_AFTER_INSERT_UPDATE_DELETE_TableB_trigger ON [dbo].[testb]
AFTER INSERT, UPDATE, DELETE
AS
print 'Running Table B After Insert/Update/Delete Trigger'
insert into dbo.TestLog (field1, field2, field3)
select testb.field1, testb.field2, getdate() as field3
from testb join inserted on testb.field2 = inserted.field2
commit

go

delete testa
where field2 in ('A','B')

go

select * from testlog

select * from testa

select * from testb

go

drop table testlog
drop table testa
drop table testb

go

 

[Vancouverite]

I can't find any information confirming this, but... did you do a RECONFIGURE after the sp_configure 'nested_triggers', 1 call?

 

[NoCoolHandle]

Me tinks that Vancouverite is rite..

you need the reconfigure

it can be executed at any time, but until it is the setting isn't written to run_value

the following script shows this
You will only ever see the 1 and 4 values in table Z

rob

create database junk
go
use junk
go

create table x (c1 int)
create table y (c1 int)
create table z (c1 int)
go
create trigger x_in
on x for insert
as
insert into y select * from inserted
go
create trigger y_in
on y for insert
as
insert into z select * from inserted
go
sp_configure 'nested triggers',1
reconfigure
go
insert into x values('1')
go
select * from x
select * from y
select * from z
go
sp_configure 'nested triggers',0
reconfigure
go
insert into x values('2')
go
select * from x
select * from y
select * from z
go
sp_configure 'nested triggers',1
go
insert into x values('3')
go
select * from x
select * from y
select * from z
go
reconfigure
go
insert into x values('4')
go
select * from x
select * from y
select * from z

 

[skuhlman]

THANKS GUYS! The RECONFIGURE did the trick. When the manual said that I didn't have to re-boot the server, I assumed, incorrectly, that it was an active property. This code did fix the problem:

sp_configure 'nested triggers',1
reconfigure