Update trigger for a table where som of the field names are not known at design time

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)

Update trigger for a table where som of the field names are not known at design time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-31 : 22:43:31

KJ writes "I have an application where the end users can add their own fields to a table. I do not want to execute the update trigger if a few of the standard fields are changes (Master and Active Flags). I retrieve the field names from the syscolumns table excluding the ones I know about then try to dynamicly check the rest. It looks like the Deleted table in out of scope with the EXEC command.

This is the error I am getting:
Msg 7218, Level 11, State 2
Site 'SELECT 'T' FROM Delete d JOIN Inserted i ON d' not found in Sysservers.

Thanks in advance,

---------Update Trigger----------------

CREATE TRIGGER ut_ApplicationTbl_u ON dbo.ApplicationTbl 
FOR UPDATE
AS

  DECLARE @AppId varchar(14)
  DECLARE @fieldname varchar(50)
  DECLARE @SQL varchar(250)

  DECLARE @Changed tinyint

  --For multiple apps being updated 
  DECLARE AppIdCursor_d CURSOR FOR 
      SELECT DISTINCT AppId FROM Deleted

  --All the fieldnames in the applicationtbl table
  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')

    OPEN AppIdCursor_d 
    OPEN curFieldNames 

    FETCH NEXT FROM AppIdCursor_d INTO @Appid

    --Loop through for each App Updated
    WHILE @@FETCH_STATUS = 0
    BEGIN

       FETCH FIRST FROM curFieldNames INTO @fieldname 
       SELECT @Changed = 0

       'Check each column to see if the data changed
       WHILE @@FETCH_STATUS = 0 AND @Changed = 0
       BEGIN

         SELECT @SQL = "SELECT 'True' " +
                       "FROM Deleted d " + 
                       "JOIN Inserted i " +
                         "ON i." + @fieldname + " = " +
                            "d." + @fieldname + " " +
                       "WHERE t.AppId = '" + @AppId +"'"

          EXECUTE @SQL

          --The field data does not match then update the date
          IF @@ROWCOUNT = 0
          BEGIN

             EXECUTE up_ChangedAppTable_iu @AppId
             SELECT @Changed = 1

          END 

          FETCH NEXT FROM curFieldNames INTO @fieldname 

       END

       FETCH NEXT FROM AppIdCursor_d INTO @Appid
    END
 
    CLOSE curFieldNames  
    DEALLOCATE curFieldNames 

    IF @@ERROR != 0
    BEGIN
       ROLLBACK TRAN
    END"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-31 : 23:21:50

yuk cursor!

You do a
select * into #deleted from deleted
then use #deleted instead of deleted.

same for inserted.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources