Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Recursive update trigger

Author  Topic  Thorkyl Starting Member 3 Posts Posted - 2002-08-05 : 14:40:51 Here is what I have: Below is the script for the table and triggers. I am trying to get a recursive update to work. eg Users changes a field and the update trigger then updates the audit columns. This needs to work for both single updates and multi updates. Any help, ides, or kicks in the rear would be helpfull. Thorkyl CREATE TABLE [dbo].[PNPR_USERS] ( [AN] [int] IDENTITY (1, 1) NOT NULL , [PNPR_USER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PNPR_USER_PWD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PNPR_USER_FNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PNPR_USER_LNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PNPR_USER_PHONE] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PNPR_USER_ACTIVE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CREATION_DT] [datetime] NULL , [APP_LOGIN_CREATION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MODIFICATION_DT] [datetime] NULL , [APP_LOGIN_MODIFICATION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[PNPR_USERS] WITH NOCHECK ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [PNPR_USER_ID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PNPR_USERS] WITH NOCHECK ADD CONSTRAINT [DF_PNPR_USERS_PNPR_USER_ACTIVE] DEFAULT ('Y') FOR [PNPR_USER_ACTIVE] GO CREATE TRIGGER [tI_PNPR_USERS] ON [dbo].[PNPR_USERS] FOR INSERT AS DECLARE @varRequired nvarchar(50) SELECT @varRequired = CREATION_DT FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET CREATION_DT = getdate() WHERE PNPR_USERS.AN = @@IDENTITY END SELECT @varRequired = MODIFICATION_DT FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET MODIFICATION_DT = getdate() WHERE PNPR_USERS.AN = @@IDENTITY END SELECT @varRequired = APP_LOGIN_CREATION FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET APP_LOGIN_CREATION = CURRENT_USER WHERE PNPR_USERS.AN = @@IDENTITY END SELECT @varRequired = APP_LOGIN_MODIFICATION FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET APP_LOGIN_MODIFICATION = CURRENT_USER WHERE PNPR_USERS.AN = @@IDENTITY END GO CREATE TRIGGER [tU_PNPR_USERS] ON [dbo].[PNPR_USERS] FOR UPDATE AS DECLARE @varRequired nvarchar(50) SELECT @varRequired = CREATION_DT FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET CREATION_DT = getdate() WHERE PNPR_USERS.AN = @@IDENTITY END SELECT @varRequired = APP_LOGIN_CREATION FROM INSERTED IF @varRequired is null BEGIN UPDATE PNPR_USERS SET APP_LOGIN_CREATION = CURRENT_USER WHERE PNPR_USERS.AN = @@IDENTITY END UPDATE PNPR_USERS SET APP_LOGIN_MODIFICATION = CURRENT_USER WHERE PNPR_USERS.AN = @@IDENTITY UPDATE PNPR_USERS SET MODIFICATION_DT = GETDATE() WHERE PNPR_USERS.AN = @@IDENTITY GO