Update trigger without knowing tables columns

[KeithOMatic]

I am attempting to write a trigger to update a change date field. The problem I'm having is that user can add columns to the table and name them anything. I am grabbing all the columns names for the table out of the system tables into a cursor. I can't seem to retrieve a column value using the variable from the deleted table to compare inorder to see if it was updated.

Here is my trigger:

CREATE TRIGGER ut_ApplicationTbl_u ON dbo.ApplicationTbl
FOR UPDATE
AS

DECLARE curFieldNames SCROLL CURSOR
FOR
SELECT c.name
FROM syscolumns c, sysobjects t
WHERE c.id = t.id and
t.name = 'ApplicationTbl' and
not(c.name = 'AppActive') and
not(c.name = 'AppMasterCopy')

DECLARE @FieldName varchar(50)
DECLARE @AppId varchar(14)

OPEN curFieldNames
FETCH FIRST FROM curFieldNames INTO @FieldName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Appid = (Select Appid From Deleted)

--I know this is wrong but don't have any ideas

SELECT @NewData = "T." + @FieldName
SELECT @OldData = "d." + @FieldName

IF NOT EXISTS
(
SELECT 'True'
FROM Deleted d
JOIN ApplicationTbl t
ON @NewData = @OldData
WHERE d.APPID = @AppId
)
BEGIN
sp_UpdateChgDate @AppId, @FieldName
END

-----

FETCH NEXT FROM curFieldNames INTO @FieldName

END

CLOSE curFieldNames
DEALLOCATE curFieldNames

IF @@ERROR != 0
BEGIN
ROLLBACK TRAN
END

Thanks in advance,

 

[Gundapaneni]

Try
SELECT @Appid=appid From Deleted
instead of
SELECT @Appid = (Select Appid From Deleted)



Madhu.