Re: Trigger Unlimited Recursion Error

From: Carl C. Federl <cfederl_at_yahoo.com>
Date: 26 Jun 2003 11:32:35 -0700
Message-ID: <db76e830.0306261032.76cc9a6d_at_posting.google.com>

> PS I searched a lot of newsgroups, but found nothing on this topic The ASA forums are excellent but not in the public newsgroup but instead are hosted by sybase. Try this URL http://www.sybase.com/support/newsgroups

The problem with recursive triggers is caused by the architecture of ASA. A similar architeture is also present with the Sybase and MS SQL Server RDBMSs.
A trigger is invoked under ASA whenever the triggering action DML statement is executed. The fact that the trigger action DML statement affect zero rows still causes the trigger to be executed. Unless a check for this condition is included in the trigger source, cascading and recursive trigger behavior will be exhibited.

For example, this update statement will always update zero rows but a trigger with an update action will still be executed.

Update AnyTable set somecolumn = 1 where 1 = 2

One solution is to check whether the NEW/OLD virtual tables have any rows and exit the trigger is they are empty.

For your particular problem of syncronizing 2 tables via triggers with either being the source, below is a SQL solution. For insert action, the trigger will execute the insert into the other table for those rows that do not exists in the other table with the same primary key. Similar logic could be used for deletes. For updates, a global temporary table is defined that is used to communicate state information between the triggers.

Good Luck

CREATE TABLE FOO

( ThePk 			integer NOT NULL
, OtherColumn   			varchar(255) NOT NULL

, PRIMARY KEY (ThePk)
)
;

CREATE TABLE FUM

( ThePk 			integer NOT NULL
, OtherColumn   			varchar(255) NOT NULL

, PRIMARY KEY (ThePk)
)
;

CREATE GLOBAL TEMPORARY TABLE FooFum_sync_state (TriggerTs timestamp NOT NULL
,ThePk integer not null
) ON COMMIT DELETE ROWS
;

create trigger FOO_ias_001 after insert order 1 on FOO referencing new as inserted
for each statement
begin
  if 0 = (select count(*) from inserted) then return end if;   insert into FUM

        (ThePk,OtherColumn)
  select ThePk, OtherColumn
  from inserted
  where not exists

        (select 1 
         from FUM
         where FUM.ThePk= inserted.ThePk
	)

end
;

create trigger FUM_ias_001 after insert order 1 on FUM referencing new as inserted
for each statement
begin
  if 0 = (select count(*) from inserted) then return end if;   insert into FOO (ThePk,OtherColumn)
  select ThePk,OtherColumn
  from inserted
  where not exists

        (select 1 
         from  FOO
         where FOO.ThePk= inserted.ThePk
	)

end
;

create trigger FOO_uas_001 after update order 1 on FOO referencing old as deleted new as inserted for each statement
begin
  declare _at_TriggerTs timestamp;
  set _at_TriggerTs = current timestamp;   if 0 = (select count(*) from inserted)

        then return end if;
-- Rows processed by the trigger are in table FooFum_sync_state -- Are there any row that have not already been processed by the trigger ?
if 0 = (select count(*)

	from inserted 
	where not exists 
		(select 1 
		from FooFum_sync_state 
		where FooFum_sync_state.ThePk= inserted.ThePk) )
	then return  end if;

-- Track the rows that will be processed

  insert into FooFum_sync_state

         (TriggerTs, ThePk)
  select _at_TriggerTs, ThePk
  from inserted
  where not exists

	(select 1
	from FooFum_sync_state
        where FooFum_sync_state.ThePk= inserted.ThePk
   	)

;

  update FUM
  set OtherColumn = inserted.OtherColumn   from inserted
  join deleted

	on  inserted.ThePk= deleted.ThePk
  	and inserted.OtherColumn <> deleted.OtherColumn
  join FooFum_sync_state
	on FooFum_sync_state.ThePk= inserted.ThePk
	and FooFum_sync_state.TriggerTs = _at_TriggerTs

  WHERE FUM.ThePk= inserted.ThePk
;

end
;

create trigger FUM_uas_001 after update order 1 on FUM referencing old as deleted new as inserted for each statement
begin
  declare _at_TriggerTs timestamp;
  set _at_TriggerTs = current timestamp;   if 0 = (select count(*) from inserted)

        then return end if;
-- Rows processed by the trigger are in table FooFum_sync_state -- Are there any row that have not already been processed by the trigger ?
if 0 = (select count(*)

	from inserted 
	where not exists 
		(select 1 
		from FooFum_sync_state 
		where FooFum_sync_state.ThePk= inserted.ThePk) )
	then return  end if;

• Track the rows that will be processed

  insert into FooFum_sync_state

         (TriggerTs, ThePk)
  select _at_TriggerTs, ThePk
  from inserted
  where not exists

	(select 1
	from FooFum_sync_state
        where FooFum_sync_state.ThePk= inserted.ThePk
   	)

;

  update FOO
  set OtherColumn = inserted.OtherColumn   from inserted
  join deleted

	on  inserted.ThePk= deleted.ThePk
  	and inserted.OtherColumn <> deleted.OtherColumn
  join FooFum_sync_state
	on FooFum_sync_state.ThePk= inserted.ThePk
	and FooFum_sync_state.TriggerTs = _at_TriggerTs

  WHERE FOO.ThePk= inserted.ThePk
;

end
go

• These are the test cases --

delete from foo;
delete from fum;
commit;
select 'FOO' , thePk, OtherColumn from FOO union all select 'FUM', thePk, OtherColumn from FUM
-- Insert a single row
insert into FOO (thePk, OtherColumn ) values ( 1 , 'First Row' ) ; select 'FOO', * from FOO union all select 'FUM', * from FUM; -- Insert a single row
insert into FOO (thePk, OtherColumn ) values ( 2 , 'Second Row' ) ; select 'FOO', * from FOO union all select 'FUM', * from FUM; -- Insert multiple rows
insert into FOO (thePk, OtherColumn )
select 3 , 'Third Row' union all
select 4 , 'Fourth Row'
;

select 'FOO', * from FOO union all select 'FUM', * from FUM; -- Update a Single Row
Update Foo set OtherColumn = OtherColumn + ' Update one row' where thePk = 2 ;
select 'FOO', * from FOO union all select 'FUM', * from FUM; -- Update Multiple rows
Update Foo set OtherColumn = OtherColumn + ' Update 2 rows' where thePk in (3, 4) ;
select 'FOO', * from FOO union all select 'FUM', * from FUM; commit; Received on Thu Jun 26 2003 - 20:32:35 CEST