if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TESTTable_INSERT]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TESTTable_INSERT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[TextField] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
	[NumericField] [int] NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER TESTTable_INSERT ON dbo.TestTable 
FOR INSERT
AS

declare @ID int
declare Cur cursor  local for SELECT ID FROM inserted
open Cur
fetch Cur into @ID

while (@@fetch_status = 0)
begin

	UPDATE dbo.TestTable SET NumericField = NumericField+1 WHERE [ID] = @ID
	fetch Cur into @ID
end

close Cur

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.CreateTrigger @Table varchar(255), @Field varchar(255) AS

declare @createtrigger varchar(3000)
SET @createtrigger=
'CREATE TRIGGER #TABLE#_INSERT ON dbo.#TABLE# 
FOR INSERT
AS

declare @ID int
declare Cur cursor  local for SELECT ID FROM inserted
open Cur
fetch Cur into @ID

while (@@fetch_status = 0)
begin

	UPDATE dbo.#TABLE# SET #FIELD# = #FIELD#+1 WHERE [ID] = @ID
	fetch Cur into @ID
end

close Cur
'

SET @createtrigger = REPLACE (@createtrigger,'#TABLE#', @Table)
SET @createtrigger = REPLACE (@createtrigger,'#FIELD#', @Field)

exec (@createtrigger)
GO

quote:

---

Originally posted by spirit1

for updating that column use this:

declare @i int
set @i = 0
UPDATE t1
SET @i = Id = @i + 1
from MyTable t1 join inserted i on i.id = t1.id

you don't need a cursor.

---

quote:

---

if you don't know the id's then you can't generalize....

Go with the flow & have fun! Else fight the flow

---

quote:

---

select distinct a.COLUMN_NAME, a.TABLE_NAME from information_schema.columns as a inner join information_schema.constraint_column_usage as b ON a.COLUMN_NAME = b.COLUMN_NAME
INNER JOIN information_schema.table_constraints as c ON b.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY' and a.TABLE_NAME ='TestTable'

---