Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.

It is supplied by a vendor (Sage \ SalesLogix) so dont blame me.

ALTER TRIGGER [sysdba].[ADDRESS_RECCHANGE] ON [sysdba].[ADDRESS]

FOR INSERT, UPDATE

AS

BEGIN

DECLARE @recid char(12)

DECLARE @conid char(12)

DECLARE @reccount integer

DECLARE tblCursor CURSOR FOR SELECT ADDRESSID FROM INSERTED

OPEN tblCursor

FETCH NEXT from tblCursor INTO @recid

WHILE (@@FETCH_STATUS = 0)

BEGIN

DECLARE tblAddr CURSOR FOR SELECT ENTITYID FROM INSERTED

OPEN tblAddr

FETCH NEXT from tblAddr INTO @conid

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- delete the old row for this record

SELECT @reccount = count(1) FROM sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid)

IF (@reccount > 0)

BEGIN

DELETE sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid)

END

-- insert new record into SLXRECCHANGE table

INSERT INTO sysdba.SLXRECCHANGE (TABLENAME, ENTITYID, MODIFYDATE, CHANGETYPE) VALUES ('CONTACT', @conid, getutcdate(), 'C')

FETCH NEXT from tblAddr INTO @conid

END --while

CLOSE tblAddr

DEALLOCATE tblAddr

FETCH NEXT from tblCursor INTO @recid

END -- while

CLOSE tblCursor

DEALLOCATE tblCursor

END