I have trigger UPDATETRIGGER on table TEST,
It was written to get called when the TEST table is updated.
Now in this UPDATETRIGGER is updating a column of the same TEST table.
Will this be recursive?
My trigger and table is in MS SQL database. From the table values i see that it is not happening such way could any one explain please.
USE [TESTING]
GO
/****** Object: Trigger [dbo].[UPDATETRIGGER] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UPDATETRIGGER] on [dbo].[TEST]
FOR UPDATE
AS
UPDATE dbo.TEST
SET lastEditedDate=GetDate()
FROM INSERTED newdata
WHERE TEST.MasterK = newdata.MasterK
MS SQL has some properties set that do not allow recursive triggers to fire unless you turn them on. Well it will fire the recursive / nested trigger 32 times and then fail out. Also this is a for update trigger not a before/after update trigger so this takes place of the update functionality itself.
After (For) / Before
These two types create functionality on top of what the database will actually do in an update. So if the before or after changes information the databases original update function will fire again and then you are well into your loop.
Instead Of
This overrides the database's normal functionality and does only what you tell it to do when an update occurs. In a database update is actually the combination of Delete/Insert. That blew my mind the first time I realized that as well.