The heart of this code is the EXCEPT clause in the cursor definition.
When I run this statement:
set nocount on
UPDATE rm00101 SET custname = 'steve' WHERE custnmbr = 'somevalidnumber'
I get these results
nest level 1
trigger start
trigger end
nest level 1
trigger start
changed:
13498
steve
nest level 2
nest level > 1, exit
trigger end
The first three lines are caused the zDT_RM00101U trigger, it updates the RM00101.DEX_ROW_TS field, so our trigger fires as a result of that update. Since CUSTNAME, CUSTNMBR are not changed, nothing happens.
The remaining lines are caused by our trigger.
Since CUSTNAME changed, we enter the cursor and we print out the new value.
Next, we cause recursion ourselves and you can see the trigger safely exiting then the NESTLEVEL is 2
In production code, we would insert into an audit table of some sort in the cursor.
We use a cursor because it is technically possible to update multiple lines at the same time... but GP never does that to my knowledge.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N't_4P_RM00101_INSUPD'
AND type = 'TR')
DROP TRIGGER t_4P_RM00101_INSUPD
GO
CREATE TRIGGER t_4P_RM00101_INSUPD
ON RM00101
FOR UPDATE , INSERT
AS
BEGIN
set nocount on
set transaction isolation level read uncommitted
--print out the NESTLEVEL
--the UPDATE statement below causes a recursion, you'll see this in the results
PRINT 'nest level ' + CONVERT(varchar(2),trigger_nestlevel(@@PROCID))
--if the next level is > 1, exit here
if trigger_nestlevel(@@PROCID) > 1 BEGIN
PRINT 'nest level > 1, exit'
return
END
PRINT 'trigger start'
DECLARE @custnmbr VARCHAR(15)
DECLARE @custname VARCHAR(65)
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
--only get rows where the custnmbr or custname have changed. ignore all other changes
--this is the heart of this code
select ins.custnmbr,ins.custname
from inserted ins
EXCEPT
select del.custnmbr,del.custname
from deleted del
OPEN cur
WHILE 1=1
BEGIN
FETCH NEXT FROM cur INTO @custnmbr, @custname
if @@fetch_status <> 0 begin
break
end
--print out the cust number, name
--this is the new value, not the old one
PRINT 'changed: '
PRINT ' ' + @custnmbr
PRINT ' ' + @custname
--updating the same table that we're triggering, this will cause recursion.
UPDATE rm00101 SET ADRSCODE = 'x' WHERE CUSTNMBR = @custnmbr
END
CLOSE cur
DEALLOCATE cur
PRINT 'trigger end'
--make the results easier to read
PRINT ''
PRINT ''
PRINT ''
PRINT ''
END
GO